Floating FB popout byF5debug

SQL Server Integration Services (SSIS) – Part 43 – Derived Column Transformations in SSIS

 

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 - http://f5debug.net/tutorial/ssis.aspx

[more]

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.

clip_image001

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

clip_image002

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

clip_image003

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

clip_image004

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

clip_image005

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

clip_image006

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.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter