SQL Server Integration Services (SSIS) - Merge Transformation in SSIS

Introduction:

In this article we are going to see how to use Merge transformation in SSIS Packaging. Merge transformation is used in cases where we need to get data from 2 different data sources and merge then in the order specified and send the result to the destination. Merge transformation can be very effective when we need to do manipulation across the data sets or the data source. Let's jump start how to use this task in real time and see the steps to do the configurations.
You can look into my series of articles on SSIS at the url - http://f5debug.net/all-articles/

Steps:

Follow steps 1 to 3 of 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 Merge Transformation task. Once you open the project just drag and drop the Merge transformation control and a source provider is shown in the image below.


Now we need to start configuring the transformation. Let us start with configuring the source data; for that we need to create a table as shown in the screen below.

Scripts:

Create table Merger1(   Empid int,EmpName Varchar(50))
Create table Merger2(   Empid int, EmpName Varchar(50))
Insert into Merger1 values (1,'Arun')
Insert into Merger1 values (3,'Karthik')
Insert into Merger1 values (4,'Amit')
Insert into Merger1 values (6,'Vinoth')
Insert into Merger2 values (2,'Vijay')
Insert into Merger2 values (3,'Karthik')
Insert into Merger2 values (5,'Ruthesh')
Insert into Merger2 values (7,'Ruthra')
Select * from Merger1
Select * from Merger2
To configure the sources we need to do the steps below. Here we are going to use a query to fetch the data as shown below:
Select Empid,EmpName From Merger1 Order by Empid
Select Empid,EmpName From Merger2 Order by Empid
Your screen looks like below after you configure the first data source.


Similarly do the second data source as shown below:


Now once the sources are configured we need to configure the Merge transformation task by mapping both the merger source with the merge as shown below.


Here we need to do some steps to make the Merge task accept the sources; for this we need to set the sorting to true. Since the task by itself will not do the sorting to merge the data. For that you need to right-click on the sources task and select "Show advanced editor" and select the input output properties tab and click on the OLEDB Source output. Then change the IsSorted property to True on the output.
Now we are done with configuration of the sources and the merge task. Now we need to get the output; for that we can use a flat file destination as shown in the screen below:


Now execute the task (Press F5); you will get the desired output as shown below.


We can see the output in the file which we specified at the configuration of the destination task.

Conclusion:

So in this article we have seen how to use the Merge transformation task and the key configurations used in order to use this task easily.