Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

SSIS - Difference between MERGE and UNION ALL components

Discussion in 'Business Intelligence' started by Peter Schmitz, Apr 13, 2019.

  1. Peter Schmitz

    Peter Schmitz Administrator 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.

Share This Page