This is part 43 of the series of article on SSIS

Introduction:

In this article we are going to see on how to use the Derived Column transformation in SSIS packaging. Derived column transformation is used in cases where we do some manipulations and get the desired result in a separate column. Say for example we need to do some transformations based on calculating the salary with some perks and have it in a separate column then we can go with the derived columns. Let’s jump start to the section on how to do that using a sample package.

You can look into my series of article on SSIS at the url – https://f5debug.net/tutorial/ssis.php

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on how to use the Derived Columns control. Once you open the project just drag and drop the Derived Column control and a source and destination provider as shown in the below image.

Now we need to do the configuration for each of the tasks, first we will start with Source. In our example we are going to create a table as shown in the below scripts

CREATE TABLE EmpDetails(EMPID int, EMPFName varchar(10), EMPLName varchar(10),

EMPDOB Datetime, EMPSal int, EMPHra int)

GO

INSERT INTO EmpDetails (EMPID, EMPFName, EMPLName, EMPDOB, EMPSal, EMPHra)VALUES(1,’Karthik’,’Anbu’,’01/01/1980′, 10000,1500)

INSERT INTO EmpDetails (EMPID, EMPFName, EMPLName, EMPDOB, EMPSal, EMPHra)VALUES(2,’Arun’,’Kumar’,’02/02/1981′, 8000,1200)

INSERT INTO EmpDetails (EMPID, EMPFName, EMPLName, EMPDOB, EMPSal, EMPHra)VALUES(3,’Ram’,’Kumar’,’01/02/1982′, 6000,1000)

Go

Now configure the source to get the details from the table above. Once the source is configured now we need to do the configuration for the destination section. So here we are going to create a new table as shown in the below script

CREATE TABLE EmpDetailsDestination (EmpFullName varchar(21), EmpAge int, EmpCTC int, InsertedDate DATETIME)

Now the records in both the source and destination tables are shown in the below screen

Our primary goal is to do some manipulations using the derived column task and save it in a separate table. So we are configure the Derived Column by double clicking the control will open the window for configuration as shown in the below screen

In the expression section if you see we have created some expressions to do some manipulations as per our requirement.

Now we need to do the configuration for the destination source by mapping the columns as shown in the below screen

Now once all the task steps are configured press F5 to build and execute the package. Once your package is executed your screen looks like below

We can see the output in the destination table as shown below

Conclusion:

So in this article we have seen on how to use the Derived Column Transformation to do some manipulation and transform data to a new column.