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.

Posted by: MR. JOYDEEP DAS