SSIS Merge component performing a UNION instead of merging

Question

I have a package to combine data from multiple databases. I'm trying to bring together two data sets so they can be written to a single table.

There's a common SSIS_ID (DT_I4) used to match rows and the other columns I want to bring in to the same data set are also DT_I4.

Here's the section of the flow where the problem is; as you can see the data from both paths is sorted on SSIS_ID before entering the Merge:

And here is the configuration of the Merge component. The intention is to put the CRO_CompanyID and Sponsor_CompanyID for the same SSIS_ID in to a single row:

However the output of the merge is acting like a UNION; there are 1,532 rows entering from the left and 4,303 from the right resulting in 5,835 rows at the output.

A data viewer on the output shows the problem:

I can get around this problem by processing the data in a different way but it's really bugging me that I can't get this to work.

Any suggestions as to what I have done wrong or not done to be able to combine the data in this way?

I'm using SSIS for SQL Server 2008 but there wasn't a tag for that version.

Answer 1

The issue is that you're using the wrong transformation.

"The Merge transformation is similar to the Union All transformations." -MSDN Merge Transformation

You can use the Merge Join transformation and specify your join type, then select the input column used for each output.

Then you can get your desired output:

If your source(s) is an RDBMS system it's better to sort with an ORDER BY as opposed to using a Sort task, since it is a blocking component and the DB can likely perform the sort faster.