SSIS – Merge Join Transformation

Requirement:

Retrieve and merge data from multiple sources

• Get data from a two data sources, SQL database, and Excel file.
• Merge the data coming from source using Merge Join.
• Generate flat file as output.
• In case of Success or failure notify through Email.

Solution:
Add Data flow task in Design surface from toolbox



Step 1

Double click on data flow task, add two or more Source database like SQL server database, Excel file and configure the source database.

Here, we are using SQL database. Hence, we will add OLE DB source from SSIS toolbox.

To configure the OLE DB source, double click on the Source and you will see window as shown in image below.



Select required columns from the table



Now configure Excel source as shown in image below.

Double click on Excel source. Window as shown in image below will appear



Click on New to give excel file path configuration as shown in image below



Step 2
In order to merge the data between two or more source, the data needs to be sorted.

Hence, we will have to use SORT function before using Merge join function.

Drag and drop SORT from SSIS toolbox in design surface.

While sorting the data, we have to give column names which are having same metadata in both the sources.

Step 3
After sorting the input data, drag and drop MERGE JOIN from SSIS toolbox



Double click on merge join to configure and merge the required columns from input. Refer below image to configure Merge Join

Note: If the metadata of the columns mismatches then it will throw an error as image below



Hence, metadata of both the columns should be same. If so then we can configure Merge Join Transformation.



Select Join type as per the requirement which is highlighted in above image and select required columns.

Types of Joins

• Inner Join: It returns matched data between two tables as per the condition given as input in Merge Join.
• Left Outer Join: It returns matched data along with the complete data from left hand side table given as input in Merge Join
• Full Outer Join: FULL OUTER JOIN returns all rows from the left table and from the right table given as Input in Merge Join.

Step 4
Add Flat file destination from SSIS toolbox and configure it as shown in image below



Step 5

Finally you will have a similar structure like image below.

Run the package.



This is how we can merge the data from two or more data sources and generate output file as per requirement in SSIS.

Points to Remember

• To merge the data from different sources, we need to SORT the data.
• While merging the data using MERGE JOIN, the columns must have same metadata.
• Give proper connection paths while configuring Source and Destinations.