MERGE JOIN TRANSFORMATION

            Using Merge Join transformation we can join two sorted data sets based on common column. We can perform “Inner join, Left outer join and Full outer join" using Merge join transformation.

Sample package creation:

Requirement:-

Two source tables are available EMP (is having Employee information) and DEPT (is having department information).

EMP Table:-

CREATE TABLE EMP (ID INT,NAME VARCHAR(20),DEPT_ID INT)

GO

INSERT INTO EMP VALUES (101,'A',10)

INSERT INTO EMP VALUES (102,'B',20)

INSERT INTO EMP VALUES (103,'C',10)

INSERT INTO EMP VALUES (104,'D',30)

DEPT Table:-

CREATE TABLE DEPT (DEPT_ID INT,LOC VARCHAR(20))

GO

INSERT INTO DEPT VALUES (10,'Location1')

INSERT INTO DEPT VALUES (20,'Location2')

INSERT INTO DEPT VALUES (30,'Location3')

Join the source tables based on ‘DEPT_ID’ column and load the source data into target table “EMP_TARGET”.

EMP_TARGET:-

CREATE TABLE EMP_TARGET (ID INT,NAME VARCHAR(20),DEPT_ID INT,LOC VARCHAR(20))

Package creation:

• Open BIDS.
• F
  rom Control flow items, drag and drop “Data flow task” into designer surface.
• From Data flow items, drag and drop tow “OLEDB Sources” into designer surface (Firs one is for “EMP Table” second one for “DEPT Table”) 
• Map the "OLEDB Sources" with source tables.
• We need to sort the source data based on ‘DEPT_ID’ column because “Merge Join” transformation accepts only sorted data sets as input.
• We can sort the source data in two ways.
  o   Using Sort transformation.
  o   Using OLEDB source “Advanced Editor”
• In this example I used ‘OLEDB Source Advanced Editor’ to sort the source data sets.
• From Data flow items, drag and drop the “Merge Join” transformation into designer surface.

• Select ‘OLEDB source’ (EMP) green arrow and connect to the transformation. Then “Input Output Selection” Window will open. In that window, Select “Merge Join Left Input” from the “Input:” drop down list.

• Select second ‘OLEDB Source’ (DEPT) green arrow and connect to the transformation.

• Right click on “Merge Join” and select “Edit” option.

• Select ‘Join type:’ as Inner join and select the required columns and click “OK” button.

• From ‘Data flow’ items, drag and drop “OLEDB Destination” into Designer surface.
• Connect the ‘Merge Join’ transformation green arrow to ‘OLEDB Destination’.
• Map the ‘OLEDB Destination’ to the target table and execute the package.

Target table Output: