SSIS – Merge Join Transformation

This article will help you to understand the concept of retrieving and merging the data from different sources and generate output file in SSIS. This will help us to merge the data from different data sources and generate output as per the requirement. We have two data sources like OLE DB source (SQL Database) and Excel Source. Using functions like SORT, Merge Join, We will merge the data and generate the flat file as output. Let's understand how we can achieve this requirement using SSIS


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.

Add Data flow task in Design surface from toolbox

Data flow task

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.

OLE DB source

Select required columns from the table

required columns

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

excel file path

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

Merge Join

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.

Merge Join Editor

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

Flat file

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.


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: