SSIS - Difference between MERGE and UNION ALL components

Peter Schmitz

Staff member
As part of studying for a Microsoft exam, I came across a particular question:

"You are planning a SQL Server Integration Services (SSIS) package. The sources have the same structure and data types.

You have the following requirements:

* The package must be able to combine data from at least three different sources.
* The data combination must be done vertically
* The transformation must produce a single dataset

You need to create the package to meet the requirements. Your solution must minimize development effort.

Which transformation or task should you use?"

Options are:

* Script
* Conditional split
* Merge
* Union All

Obviously, a script task would not meet the minimum development effort. A conditional split is also irrellevant, as that is the SSIS equivalent of a SQL CASE statement, which redirects outputs based on value found in a specific column.

This leaves either a MERGE or a UNION ALL. Initially, I got thrown off by the "data combination must be done vertically" requirement, as I actually never heard of that term. The only thing I can think off is it is some form of a pivot.

The answer is actually revealed by the first requirement. A MERGE task can only handle 2 data sources, whereas a UNION ALL can combine as many as needed.

So therefore, the answer in this case is the UNION ALL task!

The other difference between the two components, for those who want to know the differences is that a MERGE task will require the input data to be sorted. UNION ALL doesn't care.