SSIS 2008 - Derived Column Transformation

Derived Column Transformation:

As per the name whenever we want to derive a new column in our DFT based on an existing column or a static value or based on variable value, derived column transformation can be used. This transformation requires one input and provides only one output. It’s a synchronous transformation which supports Error routing.
       Below mentioned example explains in more detail various features available in derived column transformation:

Sample data:

SalesRep_Id	Month	Year	Region	Country_Code	Unit_Sold
1	Jan	2011	Asia	IN	100
2	Feb	2011	EU	UK	150
3	March	2011	US	US	175
4	Jan	2012	Asia	IND	150
5	Feb	2012	EU	UK	200
6	March	2012	US	US	100
7	Jan	2013	Asia	IN	250
8	Feb	2013	EU	uk	300
9	March	2013	EU	UK	350

Before inserting the above sample data into the target,

1   1. We will check whether the country_code is of length 2, if not then it will be redirected to error output and those with length 2 will be converted to uppercase, if they are not.

2. We will add a new column “Insert_dt” using in-built Date-Time function “GETDATE()”.

As shown in the below screenshot, above sample data is been stored in a excel file. Excel Source is used to read the data which is been mapped to Derived Column Transformation ‘Der_Transform’:

To implement the first scenario, in the derived column transformation editor, under ‘Derived Column Name’ type in ‘Der_Cntry_Code’, in ‘Derived Column’ select ‘<add as new column>’, under expression type in “(DT_STR,2,1252)(LEN(Country_Code) <= 2 ? (DT_STR,2,1252)(Country_Code == UPPER(Country_Code) ? Country_Code : UPPER(Country_Code)) : Country_Code)”. In this expression, we are checking whether the length of  input Country_Code is less than equal to 2 or not, if it is then, will check if it is in Upper case or not, if it is then the input column will be send ahead as it is, if not then it will be converted to Upper case using ‘UPPER()’ function and if the length is more than 2 then it will send unchanged.

If the incoming data is having a length more than 2, then in that case above expression will error out, resulting into its failure. To avoid this, we will ‘Configure Error Output’. From the above screen shot click on ‘Configure Error Output’. It will open ‘Configure Error Output Editor’. As shown in the below screenshot under ‘Truncation’ select ‘Redirect Row’.

This will cause the given input record route to Error Output, if its length is more than 2. Now will we map the output of the Der_Transform to two derived column transformation Valid_Country_code and other Invalid_Country_Code, Green arrow will be mapped to Valid_Country_code and  Red arrow (Error handling) to Invalid_Country_Code.

Now to implement the second scenario, we will again open the Der_Transform. As shown in the below screenshot we will name the new column as ‘Der_Insert_DT’. In Derived Column ‘<add as new column>’ will be selected. Under Expression in-built function ‘GETDATE()’ will be used which can be found under ‘Date/Time Functions.’

Output: