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