Step by Step SSIS–Derived Column Transformation TIP # 120

Rajat-Indiandotnet

1.00/5 (1 vote)

26 Jan 2016CPOL3 min read

13.1K

In this post, we will try to understand what is Derived Column Transformation with the help of a real world example.

In the series of Step by Step SSIS, Zero to Hero in SSIS, this is another post in which we will try to understand what is Derived Column Transformation.

Let’s understand this with the help of a real world example. Suppose we are shopkeepers and our work is buy things from carpenters and sell them by adding our 20% margin.

So, carpenters give us source data (file, SQL Server, etc.) with their rate according to the product. What we do actually is we add another column in our register by adding 20% which is our selling price. So, our selling price column is a derived column here which we derived from the source by adding some of our operations.

Isn’t it simple?

Let’s understand this now step by step. I am using Adventureworks2012 database here as a source database and using product & ProductCategory table combination query. So, assume this source data is data of carpenter’s products. Here, if you see the below image, we have productId, Product Name, color, Category & ListPrice (price at which we (Shopkeeper bought the product)).

Step 1

It is useless to say here add a new Package in your project. Drag drop data flow task on canvas. Now double click the data flow task, you will get a new screen where you need to drag drop source Assistance.

Now, you need to configure source assistance where database will be adventurework2012. If you see below, we have the same columns which we shared earlier in the figure.

Step 2

I am sure you will not face any problem in the configuration of source control. Now in step 2, we have to drag drop Derived column. Once you drag drop derived column, you need to provide output of source assistance to Derived Column Transformation. Now to configure this, use context menu by right clicking and choose Edit option. You will get the below screen. Here, we have different functions and operators which can be utilized with columns and variables and parameters to create derived column.

If you see the above highlighted row in image, we can add a new column or can replace any existing column as shown in drop down.

Step 3

If you see, there is an expression column also where we can add custom expression. Here, we are multiplying 0.20 in list price and adding it again in Listprice and aliasing this new column SellingRate as shown in the below figure.

If you see the below screenshot, we can add other functions, operators also.

Step 4

I hope the above steps are pretty much clear. Now, add a destination file in which we will get derived column “SellingRate”. We have to configure the derived column. As shown in the below figure, we configured derived column.

Step 5

Now, once the destination file is configured, run the package by pressing F5 or clicking run option. If everything is working fine, then we will get all the green checkbox sign as shown in the below figure: