Merge Transformation in SSIS

Merge Transformation in SSIS is used to merge two inputs (such as tables or files) and produce one output. Merge Transformation is very useful when we want to combine the error path data (after handling the errors) and normal data. This SSIS Merge transformation uses key column values to insert the data into destination columns.

For example, we split the data using a Conditional Split Transformation based on the condition. After performing a few more operations on them, what if we want to merge them back?. In these situations, we can use SSIS Merge Transformation to Merge them back.

NOTE: SSIS Merge Transformation will produce only one output, and it does not support an error output.

Difference Between Merge and Union All Transformation

The SSIS Merge Transformation is similar to Union All Transformation in SSIS, but Merge has some restrictions:

Merge Transformation in SSISUnion All Transformation in SSIS
Data should be in sorted order before applying Merge TransformationNo need to sort the data
The output of the Merge Transformation will produce Sorted dataOutput will be unsorted data.
It only accepts 2 inputsIt can take more than two inputs

Merge Transformation in SSIS Example

We have sales data of products stored in two different SQL Server tables, such as Sales 2, Sales 3. And we want to merge all the related information into one database table.

STEP 1: Drag and drop the data flow task from the toolbox to the control flow region and rename it as Merge Transformation in SSIS

Merge Transformation in SSIS 1

Double click on the data flow task will take us to the Data flow region.

STEP 2: Drag and Drop two OLE DB Sources from the toolbox to the data flow region. Then double-click on the first OLE DB source in the data flow region opens the connection manager settings and provides an option to select the table holding the source data. Here we are selecting Sales2 table as a source

Merge Transformation in SSIS 2

STEP 3: Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.

Merge Transformation in SSIS 3

Double click on second OLE DB source to configure it with Sales 2 table in SQL Server.

Merge Transformation in SSIS 4

Repeat STEP 3 to verify the columns

STEP 4: Drag and drop two Sort Transformations to sort the SQL Server table’s inputs. Please refer to Sort Transformation in SSIS article to understand the configuration of Sorter Transformation

Merge Transformation in SSIS 5

STEP 5: Double click on the sort transformation to configure it. Here we are sorting by color and then by English product name in ascending order

Merge Transformation in SSIS 6

TIP: SSIS Merge transformation will not work without sorting the input rows. So sort transformation is mandatory before merge

Do the same for Sort Transformation1 also.

Merge Transformation in SSIS 7

STEP 6: Now drag and drop the SSIS Merge Transformation to combine the sorted tables into one table

When you drag the output arrow of the Sort Transformation into Merge Transformation, an Input Output Selection window will appear to select whether it is Merge Input 1 or Merge Input 2 as shown in the below screenshot

Merge Transformation in SSIS 8

Click ok and drag and drop the output arrow of the Sort Transformation 1. Here Merge Input 2 is automatically selected by the intelligence.

STEP 7: Double click on SSIS Merge Transformation and configure it. Data inserted into the destination table based on the Color and English Product Name columns.

Merge Transformation in SSIS 9

STEP 8: Drag and drop the OLE DB destination into the data flow region and double-click on it to configure the destination server and destination table to store the merge transformation output

Merge Transformation in SSIS 10

Here we selected [SSIS Tutorials] as database and [Merge Transformation in SSIS] as the destination table

STEP 9: Check whether input columns are matching with the destination columns using Mappings Tab

Merge Transformation in SSIS 11

We finished designing the SSIS Merge Transformation package. Let us execute the package

SSIS Merge Transformation 12

Now we will check the SSIS Merge output in SQL Management studio

Merge Transformation in SSIS 13