Replace one code with another using a translation table

[Andy Nieubourg]

We're currently working on populating a datawarehouse using SSIS. The goal is to combine data from several systems and store it in the DW. Those different systems use different codes for the same thing, we have a translation tables in Excel (Old value - New value). How do i translate the old value to the new value (the export from the old system is in CSV).

Situation:

Flat file Source (CSV) with old codes

Excel Source with translation table

-> Merge? or some other task or combination of tasks -> OLE DB destination (Datawarehouse)



Which Data flow transformation or combination of data flow transformations do i have to use to achieve this?

Thanks in advance.

Andy

[Andy Nieubourg]

I found the solution in the mean while, it's described here:

http://pragmaticworks.com/forum/yaf_postst48_SSIS-2008-Using-Excel-in-a-Lookup-Tansform.aspx

It might be helpful for others with the same problem so I thought i'd post it here.