DERIVED COLUMN TRANSFORMATION

Introduction:

            The derived column transformation creates new columns by applying expressions (expression can contain any combination of variables, functions, operations, and columns from the transformation input) to the source columns.

The result can be added as a new column or inserted into an existing column as a replacement value.

Sample package:

Requirement:-

            I have source table with three columns (ID, First_Name, and Last_Name) shown below screen shot.

I need to load source data into target table with two additional columns.

Full_Name: Full name is a combination of first name and last name.

Load_Time: Load time is current package execution time stamp.

Package creation:-

• From control flow items, drag and drop “Data flow task” into designer surface.
• Right click on “Data flow task” and select edit option then it will go to “Data flow”
• From data flow items, drag and drop “OLEDB source” into designer surface. Create the source table mappings.

• After source mappings, drag and drop the “Derived column” transformation into designer surface. Connect the source green arrow to the transformation shown below screen shot.

• Double click on “Derived column” transformation, then “Derived column transformation editor” window will open, shown below.

• In the expression box we need to write required expression.
• Full_Name: expand the columns shown below screen shot.

• Drag and drop the “First_Name” column into the “Expression” box after that “Last_Name” into the same box (After the column “First_Name”). Use concatenation (+) between the two columns ([First_Name]+ [Last_Name]). See the below screen shot.

• Update the “Derived Column Name” box with appropriate name (Full_Name).

• “Full_Name” column creation is finished. We need to create “Load_Time” column, for that expand the “Date/Time Functions”. Drag and drop the GETDATE() function into the expression box and give the appropriate name (Load_Time) to that column.

• Click “OK”.
• Drag and drop the “OLEDB destination” into designer surface, and connect the “Derived column” transformation green arrow to the “Destination”.
• Edit the “OLEDB destination” and give the mappings.

• After target mappings, execute the package.

Target table output:

Note:

If you want space between the “first name and last name” then use below expression.

Expression: [First_Name]+” “+[Last_Name]

Output: