Derived Column Transformation in SSIS

The Derived Column transformation creates new column values by applying expressions to transformation input columns. An expression can contain any combination of variables, functions, operators, and columns from the transformation input. The result can be added as a new column or inserted into an existing column as a replacement value. The Derived Column transformation can define multiple derived columns, and any variable or input columns can appear in multiple expressions.

This is used to derive new columns by using any of the following.

Ø  Constants or static values

Ø  Other columns

Ø  Variable values

This is powerful because we can generate new column run time and can make use of the same. Lets see some of examples.

Suppose we have two columns First name and Last name in source and we want to get output as full Name so we need to add one column which hold the value of both column. This can be achieved derived column transformation.

Full name= First name + ‘ ‘+ Last name

 Similarly we have lot of examples where we need to use derived columns transformation.

In derived column transformation, there are different types of functions and operators are available those can help us to achieve this required results

Ø  Mathematical Function

Ø  String Functions

Ø  Date/Time Functions

Ø  Null Functions

Ø  Type Casts Functions

Ø  Operators

 Step by step how to use Derived column transformation in the package

In this example I am fetching the ProductKey ,SalesAmount ,TaxAmt,Freight these columns from data base and based on the value I am calculating the net sales amount and tax%.

Take data flow task.

Double click on the data flow task.

In Data flow take data flow source OLEDB data source. And make database connection.

After that I am taking the copy column transformation.

After that I am taking Derived column transformation.

Here I am adding two new columns.

 I am fetching  ProductKey ,SalesAmount ,TaxAmt,Freight these columns from data base and now we adding two extra columns (Tax% and NewSalesAmount) in destination.

Now I am taking data flow destination as Excel.

Make the data flow destination connection.

Now execute the package.

Package executed successfully.

Now see the output.