Difference Between Merge and Union All

The Merge Transform can merge data from two paths into a single output. The transform is

useful when you wish to break out your Data Flow into a path that handles certain errors and
then merge it back into the main Data Flow downstream after the errors have been handled. It
s also useful if you wish to merge data from two Data Sources.

The data must be sorted before the Merge Transform. You can do this by using the Sort

Transform prior to the merge or by specifying an ORDER BY clause in the source connection.
The metadata must be the same between both paths. For example, the CustomerID column
can’t be a numeric column in one path and a character column in another path.

The Union All Transform works much the same way as the Merge Transform, but it does not

require the data be sorted. It takes the outputs from multiple sources or transforms and
combines them into a single result set.

Label: SSIS Interview Questions