Derived Column and Union All Transformation task in SSIS 2008

Many times when we are trying to get data from different sources, there is a possibility to have same key fields. To eliminate this kind of problem, “Derived Column” transformation task is very useful. For example one company has factory at one location and back office work performed from other office. Both locations has their own employee table with IDENTITY as a primary key. Obviously Identity key generally starts with 1 by default so both location would have 1,2,3 etc. in their employee table. When you try to move both the table at one single destination, you might come across with situation where employee ID 1 would have two employee and sample with many more IDs. To eliminate this situation, you can have one more column which is not exists in source table which describes where this data come from so you have unique combination of that new column and an existing EmployeeID column.

Let us see how we could perform this. Before we head towards SSIS package development, let us first create two different table in SQL Server and one blank table as a destination of these both table. You can use below script to generate those table with/without data in your SQL Server database.

create table ForDerivedColumn1

(

      EmpID INT Identity(1,1),

      EmpName varchar(10)

)

INSERT INTO ForDerivedColumn1

SELECT 'Ritesh' union all

SELECT 'Rajan'

GO

create table ForDerivedColumn2

(

      EmpID INT Identity(1,1),

      EmpName varchar(10)

)

INSERT INTO ForDerivedColumn2

SELECT 'Rushik' union all

SELECT 'Rajvi' 

GO

create table ForDerivedColumnNewTable

(

      SourceID INT,

      EmpID INT,

      EmpName varchar(10)

)

GO

Now, open BIDS (Business Intelligence Studio) and create new SSIS project. Draw dataflow task in your “Control Tab” and double click on it to configure it which will redirect you to “Data Flow” tab. Since we have two different SQL Server table as an source, let us first create two “Ado Net Source” in our work place and select your database and table as a source. Below screen shot will show you how to configure “Ado NET Source”. I am showing example for FIRST “ADO NET Source” you have to do it for second “ADO NET Source” too with table name “ForDerivedColumn2”.

Now take two “Derived Column” transformation task and put them under “ADO NET Source” task, double click on it to configure.

Above screen capture showing you configuration for first “DerivedColumn” do it for second “DerivedColumn” task too with value 2 in SourceID column.

Once we have our dataset ready with Derived Column, we have to merge both dataset with “UnionALL” Transformation task. “UnionAll” work exactly same as “UNION ALL” in TSQL. So drag “UnionAll” transformation task below both “DerivedColumn” task and join extended green arrow from both “DerivedColumn” to “UnionAll” task.

Now, this is a time to convert the data in proper format with “DataConversion” transformation task as many time from different sources, same kind of data coming with different datatype, let us convert it with “DataConversion” task by putting it under “UnionAll” task and join extended green arrow from “UnionAll” task to “DataConversion”.

Now, we have to perform our last steps. Data is ready to go anywhere but we have to tell where it suppose to go. Since we want it to insert it in our SQL Server table, we are going to use “SQL Server Destination” task. Double click on it to configure it. Give your database and table reference to it as below image.

Once you give reference of your table, you have to go to “Mappings” tab in the same dialog box and set the mappings there so that proper data get inserted in proper place. Look at the below image for more detailed information.

Finally you are done with package now run it by hitting F5 key and check your database table whether you have actually received the data inside or not. After running if you get “Green Signal” in every task, J you are done.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com