0

I have two CSV files extracted from an operationnal database. One CSV is listing ProductsID that have been sold and the product category Name in a foreign language The second CSV has two columns, one with the full list of product category Name available in this foreign language and the second column gives the Name translated in English. How can I manage with SSIS to create a derived column in the fisrt CSV and have the corresponding Product Cat Name English?

Thanks

FeniceFenice
  • 3
  • 2

1 Answers1

0

To answer your question you could use Merge Join Transformation in SSIS to do that. Here is a blog post with a walk through on how to do that.

I would avoid doing that though. Instead, land both files into two separate tables and then join them with a query. This will be much simpler to do and it will run faster,

Chris Albert
  • 2,462
  • 8
  • 27
  • 31
  • Hi Chris Thanks for your reply. Actually this manipulation is just for a case study so maybe the first solution would be much easier for me. My DWH has a Product Dimension table with the appropriate column for Product Cat foreign language and Product Cat Eng. There is no reference to a separate Product Cat table. Also there is no ID referencing each product cat and its relative translation. So you mean I may just create two 'staging table' in SQL Server and populate them with CSV files columns then run a query to join on the Product Cat Name? – FeniceFenice Mar 28 '20 at 21:21
  • @FeniceFenice That would be the easiest thing to do. Stage the data and then write a query to do the rest of the work. Doing all that in SSIS is an overly complicated solution to the problem. If this translation isn't in your DWH I would consider adding it to your existing structure so you don't have this issue in the future. – Chris Albert Mar 29 '20 at 02:07
  • Hi Chris, Thanks for your help and suggestions I will make good usage of them. – FeniceFenice Mar 29 '20 at 08:05