MSBI : SQL Server Integration Service (SSIS) - Merge Transformation

The Merge Transformation merges two sorted data sets and outputs a sorted data set and by Merge it really means a “Sorted Union All”. You provide it with the two sorted sets, specify the keys on which they are sorted, and the “merge” then takes place, ordering the output on those keys. In my example package, I merge two datasets – each with 5 rows – and get a 10 row dataset as output.

Create a OLE DB connection to Connect the Customer_SRC_1 tabel.

Through the connection Manager You can preview the data as below.

Create a OLE DB connection to Connect the Customer_SRC_2 tabel.

Through the connection Manager You can preview the data as below.

Drag and Drop two sort component to Data flow task area and create link as below.

In the Sort Transformation editor specify the ID as the sorting column.(configure the both Sort Transformation)

Drag and drop the Merge component to Data flow task.and create a link with sort component.

Configure the input output editor as below for both Sort component and Merge Component.

Then you can see the mapping as below.

Then Drag and Drop the OLE DB Destination and connect as below.

Through the Connection manager, connect to the destination table as 'CUSTOMERS_DEST'.

Then execute the package and output will be as below.

According to the below output 10 rows available in the Destination table.

Thank you !!!!