SQL Server Integration Services (SSIS) - Derived Column Transformations in SSIS

Introduction:

In this article we are going to see how to use the Derived Column transformation in SSIS packaging. Derived Column transformations are 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 articles on SSIS at the url - http://f5debug.net/all-articles/

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on an integration services project. Once the project is created, we will see 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 are created as shown in the image below.


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 scripts below:
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 script below:
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 screen below:


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


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 screen below:


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 how to use the Derived Column Transformation to do some manipulation and then transform data to a new column.