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.

  

Run SSIS Package from command line

The dtexec command prompt utility is used to configure and execute SQL Server Integration Services packages. The dtexec utility provides access to all the package configuration and execution features, such as connections, properties, variables, logging, and progress indicators. 

Here I am creating one package and calling that package in command line.

I am creating a File task package.

Make source and destination connection.

Build the package.

Now open the command prompt.

Come to package directory.

Navigate the package existing directory.

Now write the command to execute the package.

Press enter

A: start time

B: Progress

C: source (control type)

D: status

E: Package execution time

F: Package execution finished time

G: Elapsed Time (Time taken to complete the package execution)

Now see the output

Before package execution

After package execution. 

Files are copied on the destination folder.

Hope this is will be helpful for you.