SSIS – Difference between Merge and Merge Join

We can use Merge or Merge Join transformation to combine data in SSIS. Lets look at what is the difference between these two transformations and how to decide which transformation to be used.

What is Merge Transformation

Merge transformation merge the data from 2 Datasets into a single dataset based on values in their key columns.

• Both the inputs should be in sorted order
• Metadata for all columns in the 2 datasets should be  same. For example, you cannot merge a column that has a numeric data type with a column that has a character data type
• If the data has a string data type, the length of the column in the second input must be less than or equal to the length of the column in the first input with which it is merged.

The Merge transformation is similar to the Union All transformations. Use the Union All transformation instead of the Merge transformation in the following situations:

• The transformation inputs are not sorted.
• The combined output does not need to be sorted.
• The transformation has more than two inputs.

What is SSIS Merge Join Transformation

Merge Join transformation merge the 2 sorted datasets and gives output as single data set by joining on FULL or LEFT or INNER JOIN. The joining columns in both the datasets should be in sorted order and same meta datatype. This transformation has two inputs and one output. It does not support an error output.

• Merge Join Transformation requires sorted data for its inputs.
• Merge Join transformation requires that the joined columns have matching metadata.

You can configure the Merge Join transformation in the following ways:

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