Merge Transformation & Sort Transformation

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.

SSIS Merge Transformation

Introduction

Merge Transformation is used when we get data from two different data source and merge them in order specified and load it into destination. The Merge Join transformation provides an output that is generated by joining two sorted data sets using a FULL, LEFT, or INNER join. This is similar to database join operation.

This transformation has two inputs and one output. It does not support an error output.

Case Study

Here we have two table objects called “tbl_Merge_Employee” and “tbl_Merge_Dept” contains records employee and department.

According to the Employee Code sorting at ascending order we merge them both (JOIN) and store the output into a flat file called MergeResult.txt.

How to Configure it

Step-1 [ Creating the Source Table Objects ]

-- Create the Base Table

IF OBJECT_ID(N'tbl_Merge_Employee', N'U') IS NOT NULL

   BEGIN

      DROP TABLE tbl_Merge_Employee;

   END

GO

CREATE TABLE tbl_Merge_Employee

       (EMPCODE     INT         NOT NULL PRIMARY KEY,

        EMPNAME     VARCHAR(50)  NOT NULL,

        EMPGRADE    VARCHAR(1)   NOT NULL);

GO

IF OBJECT_ID(N'tbl_Merge_2', N'U') IS NOT NULL

   BEGIN

      DROP TABLE tbl_Merge_Dept;

   END

GO

CREATE TABLE tbl_Merge_Dept

       (EMPCODE     INT          NOT NULL PRIMARY KEY,

        EMPDEPT     VARCHAR(50)  NOT NULL);

GO  

-- Inserting Records

INSERT INTO tbl_Merge_Employee

       (EMPCODE, EMPNAME, EMPGRADE)

VALUES (1, 'Joydeep Das', 'A'),

       (2, 'Sukamal Jana', 'A'),              

       (3, 'Sangram Jit', 'B'),

       (4, 'Debayan Biswas','C');

GO

INSERT INTO tbl_Merge_Dept

       (EMPCODE, EMPDEPT)

VALUES (1, 'Development'),

       (2, 'Development'),              

       (3, 'Implementation'),

       (4, 'Support');      

-- Retriving Records

SELECT EMPCODE, EMPNAME, EMPGRADE FROM tbl_Merge_Employee;

SELECT EMPCODE, EMPDEPT FROM tbl_Merge_Dept;     

EMPCODE           EMPNAME                          EMPGRADE

1                              Joydeep Das                      A

2                              Sukamal Jana                     A

3                              Sangram Jit                        B

4                              Debayan Biswas                C

EMPCODE           EMPDEPT

1                              Development

2                              Development

3                              Implementation

4                              Support

Step-2 [ SSIS Data Flow Tasks ]

Step-3 [ OLEDB Source Editor Configuration ]

Step-4 [ Sort Transform Editor Configuration ]

Step-5 [ Merge Join Transform Editor Configuration ]

Step-6 [ Flat File Destination Editor Configuration ]

Step-7 [ Run the SSIS Package ]

Step-8 [ Analyze the Flat file destination ]

Hope you like it.