Merge Transformation & Sort Transformation

Sort  Transformation
- It takes an input and sorts in ascending / descending order based on specified columns
- more than 1 columns with different sorting order can be specified

Merge Transformation
- It takes exactly 2 SORTED inputs and produces a combined(merged) sorted output
- in simple word / t-sql language, it is nothing but "sorted union all"
- Inputs has to be sorted first then only we can do Merge

EXAMPLE


Pre-requisite

Following script has to be executed

CREATE TABLE MergeDemo1 (MID INT, MName VARCHAR(10))

CREATE TABLE MergeDemo2 (MID INT, MName VARCHAR(10))

CREATE TABLE MergeDemoOutput (MID INT, MName VARCHAR(10))

INSERT INTO MergeDemo1 (MID,MName) VALUES (1,'Nisarg'),(4,'Megha'),(5,'Swara')

INSERT INTO MergeDemo2 (MID,MName) VALUES (2,'Kamlesh'),(3,'Pratibha'),(6,'Nidhi')

Steps

1. Add Data Flow Task in Control Flow
2. Drag 2 sources in DFT and it should point to MergeDemo1 and MergeDemo2 respectively.
3. Drag Sort transformation and connect it with Source1.
4. In Sort transformation, select MID column and specify sorting order as shown below.

5. whatever the columns we check in available input columns, same will be listed below. We can specify different output alias and sorting type.
6. Add another Sort transformation and connect it with second Source
7. Drag Merge Transformation and connect it with 1st Sort transformation and select Merge Input 1.

8. Connect Merge transformation with 2nd Sort transformation.
9. Drag Destination and connect it with Merge Transformation. Destination should point to MergeDemoOutput table.
10. Whole structure will look like this.

11. Execution of package will feed the data in output table like this.