Create a column to the correspondence between the code and name of competency centers
Hello, I have a package in which I extract from an Excel file a table that contains a column containing the code of competency center , I want to add another column that contains the name of the Competency Centre; I have five codes of competency centers and the name for each code. I wonder if there is a SSIS transformation that allows to create this column and to specify for each code the name. And thank you in advance.
April 26th, 2011 7:24am

You can achive this by using a lookup transformation. 1. Add a lookup transaformation control. 2. Double click and go to properties. On the general tab, specify "how to handle the rows with no match entries". If the business rules says the competency code should be a defined one, then you can set this to "fail component" so if any rows come with an undefined competency code, you package would fail and thus you will be notified. If the requirement is only to map the available codes, you can set this to "ignore failure" 3. Click on the connection tab, select a connection and specify the table/view or the query. The table/view or query has to include the competency code and name. For example like select competency_centre, competency_name from competency_definitions 4. Now click on the columns tab. You would notice the available input columns and the available lookup columns. Drag the competency code from the available columns to the competency code of lookup columns and create a link. Select check mark for the competency_name in the available lookup columns. Now the output from the lookup transformation will also incllude the competency name field. Hope this helps. Please feel free to discuss if you have any questions. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH
Free Windows Admin Tool Kit Click here and download it now
April 26th, 2011 8:34am

Thank you very much, it works very well.
April 26th, 2011 10:32am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics