Union All vs Merge Transformation in SSIS

Main differences:

Union All Merge

Sorted input: not requires requires

Number of inputs: unlimited only two

Sorted output: not generates generates

Union All Transformation is an SSIS transformation that combine multiple inputs into sigle output.

Merge Transformation is an SSIS conponent that conbines two sorted intups into a single output.

Merge Transformation

UNION ALL

Source data:

I will use 4 data sources. Two tables have 7 columns and 2 table have 5 columns. There will be some null values retrived.

Step 1:

Add 4 data sources

Step 2:

Add Union All transformation. The very first column in the Union All transformation [on the left side] is the OUTPUT COLUMN NAME. The columns on the right side from output are the input columns coming from the data sources. All data types must mutch. Some tables have missing columns, these values will be retrieved as a nulls.

You must match the input columns with the output column. If having different data types use Data Conversion conponent to match the data types.

Run the package.

Merge Transformation

Source data coming from SQL Table. Second table MatchedRecords has two extra columns.

Step 1:

Add input tables

Step 2:

Add Sort transformation. I have chosen ID column as the column to sort the data. You can pick multiple columns.

Step 2.1

Or you can sort the data on the server using SQL statement:

SELECT * FROM MatchedRecords

order by Id

Then right click on the data source Show Advance Editor for OLE DB Source > Input and Output Properties > Common Properties:

IsSorted: True

SortKeyPosition: 1 [under ID column]

Step 3:

If you place the source with extra two columns as a first Input you will be able to retrieve those 2 extra columns as well. If you would place that table as a Input 2 those two columns wont be displayed in the Input.

Table MatchedRecords has sorted input (Step 2.1), doesn't need Sort Trnasformation.

Step 4:

Run the package.