SSIS 2008 - Merge Transformation

MERGE Transformation:

This SSIS transformation is used to merge two SORTED datasets. Remember Merge transformation cannot be used on Un-Sorted datasets. To use Merge transformation:

       - Same numbers of columns should be present in both sources.
       - Data-types should match for mapping columns.
       - Data should be sorted.
       - It does not eliminate duplicates.

In the below example, we will merge two excel datasets:

Dataset 1:

Id	Name
1	Test User 1
7	Test user 7
3	Test User 3
5	Test User 5
9	Test user 9

Dataset 2:

Id	Name
2	Test User 2
6	Test User 6
8	Test User 8
4	Test user 4
10	Test User 10

As shown below, use ‘Excel source’ to configure above datasets and sort these datasets using ‘SORT transform’ using ‘ID’ column as the ‘Sort Key’ (Don’t know how to configure SORT transformation? Refer post: SSIS 2008 – SORT Transformation):

Now drag, ‘MERGE’ transform from Data Flow Transformation, map the output of Sort to Merge Transform input ‘Merge Input 1’

Similarly, map the output of other Sort transform to Merge Transform this will be mapped to ‘Merge Input 2’

In Merge Transform Editor, map the ‘Merge Input 1’ and ‘Merge Input 2’ as shown below:

Add a ‘Derived Column’ transform and configure the Data viewer to view the data:

Execute the data flow:

In the data viewer, we can see both datasets are merged in sorted order.

This transformation is almost similar to ‘UNION ALL’ transform with few differences:

MERGE Transformation	UNION ALL Transformation
Sorted data mandatory	Sorted data not a mandatory requirement
Merges only two datasets	Can merge more than two datasets.