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.
