MERGE TRANSFORMATION

The “Merge transformation” combines two sorted data sets into single sorted data set. The Merge transformation accepts only two sorted data sets as input. It combines the source data sets into single data set and sends to the transformation output.

            If we don’t want to sort the data sets or having more than 2 source data sets, then we can use “Union all” transformation to combine the data sets.

Sample package:

Requirement:-

I am having two tables (Product1, Product2), those are having product information. I need to create a file with product information from the two product tables.

Source table creation script:-

Product1:-

CREATE TABLE Product1(ID int,Name varchar(20),Price decimal(8,2))

go

insert into Product1 values (101,'Salt',10)

insert into Product1 values (102,'Rice',30)

insert into Product1 values (103,'Oil',60)

Product2:-

CREATE TABLE Product2(ID int,Name varchar(20),Price decimal(8,2))

go

insert into Product2 values (201,'Pen',10)

insert into Product2 values (202,'Book',30)

insert into Product2 values (203,'Bag',60)

Package creation:-

• Open BIDS.
• From Control flow drag and drop “Data flow task” into designer surface.
• Go to “Data flow”, from data flow items drag and drop two ‘OLEDB sources’ into designer surface (one is for Product1 other one is for Product2).
• Map the OLEDB sources with source tables.
• We need to sort the source data because “Merge” transformation accepts only sorted data sets as input. Based on “ID” column I am going to sort the source data sets.
• We can sort the source data in two ways
• Using Sort transformation
• Using OLEDB source “Advanced Editor”
• In this example I used ‘OLEDB source Advanced Editor’ to sort the source data sets.
• From data flow items drag and drop the “Merge” transformation into designer surface. 

• Select one of 'OLEDB sources' green arrow and connect to the transformation. Then “Input Output Selection” window will open. From “Input:” drop down list box select “Merge Input 1” and then click “OK”.

• Select the second OLEDB source green arrow and connect to the “Merge” transformation.

 

• Right cilck on “Merge” transfomation and select “Edit” option, then “Merge Transformation Editor” window will open.Check the column mappings and click “OK” button.

• From data flow items drag and drop the “Flat File Destination” into designer surface. Connect the “Merge transformation” green arrow to the destination.
• Configure the “Flat File Destination” and then execute the package.  

Output File: