This is part 50 of the series of article on SSIS

Introduction:

In this article we are going to see on how to use Merge transformation in SSIS Packaging. Merge transformation is used in cases where we need to get data from 2 different data source and merge in 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 on how to use this task in real time and see the steps to do the configurations. 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 Merge Transformation task. Once you open the project just drag and drop the Merge transformation control and a source provider as shown in the below image.

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

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 below steps. 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 configured the sources 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 do take the Merge task accept the sources, for this we need to give the sorting to true. Since the task by itself will not make 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 below screen

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

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

Conclusion:

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