SSIS Tutorials - Merge Join || Combine data based on conditions

In this tutorial, we will learn " How to merge data from two homogeneous or heterogeneous source" by using Merge Join Transformation in your SSIS Package. 

SSIS merge join also combines data from different sources (Source of same type or different type (heterogeneous)) into single output or merged output. It uses merge concept based on (Inner, Left and full). We can use Merge Join based on specific condition like combining data on matching keys with that Inner, Left and full. Merge Join component accepts only 2 sorted (compulsory) inputs and one output and one error output.

Unlike Merge, Merge Join combines data depending on matching keys or string name.

Used Components -

1. Data Flow Task

2. Source Assistants - Flat File and SQL Server

3. Sort Transformation : To Sort the data

4. Merge Join Transformation : Merge the sorted inputs

5. Row Sampling: To watch the final output

What is the difference between merge and merge join in SSIS?

Merge transformation in a data flow, we can perform the following tasks:

1. Merge data from two data sources, such as tables and files.
2. Create complex data-sets by nesting Merge transformations.
3. Re-merge rows after correcting errors in the data.

Merge Join transformation in a data flow, we can perform the following tasks:

1. Specify the join is a FULL, LEFT, or INNER join.
2. Specify the columns the join uses.
3. Specify whether the transformation handles null values as equal to other nulls.

SSIS Tutorials - Merge Transformation || Merge data from two sources

In this tutorial, we will learn "How to merge data from two homogeneous or heterogeneous source" by using Merge Transformation in your SSIS Package. 

Ideal Problem: If you have 2 separate csv files/text files/excel files, one with multiple headings and one with just 2 headings, both have one heading with the same name and data underneath. Here, you want to create just one file from both files before processing it further.
Solution: Use Merge Transformation to create a just one file

What is Merge Transformation?
SSIS merge works similar way to a SQL join it merges the two or more different sources (Sources can be of same type or different type / heterogeneous) into a single output. SSIS is all about collecting data from different sources, If you want to merge the collected data from different sources then we can use merge component. Merge component accepts only 2 sorted (compulsory) inputs. if there is more than 2 inputs then it best to use Union All transformation component. Also Merge transformation have only 1 output and does not have any Error output.

Before using merge transformation, you should take care of the following things-

1. Data should be sorted in the both sources
2. Field data type must be in the same type in both sorted outputs
3. Merge transformation have only 1 output 
4. Does not have any error output

Used Components -

1. Data Flow Task

2. Source Assistants - Flat File and SQL Server

3. Sort Transformation : To Sort the data 

4. Merge Transformation : Merge the sorted inputs

5. Row Sampling: To watch the final output.

To see the demo, please visit us YouTube channel -

What is the difference between merge and merge join in SSIS?

Merge transformation in a data flow, we can perform the following tasks:

1. Merge data from two data sources, such as tables and files.
2. Create complex data-sets by nesting Merge transformations.
3. Re-merge rows after correcting errors in the data.

Merge Join transformation in a data flow, we can perform the following tasks:

1. Specify the join is a FULL, LEFT, or INNER join.
2. Specify the columns the join uses.
3. Specify whether the transformation handles null values as equal to other nulls.