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:

SSIS data flow

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:

Merge configuration

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:

enter image description here

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.


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.

enter image description here

Then you can get your desired output: enter image description here

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.

  • I tried changing the 'Merge' for a 'Merge Join' but I was not able to get the result set I wanted. So I left the 'Merge' in place and worked around it. Having said that, your answer is correct in that a 'Merge' is a 'UNION' task, so really I should've approached the problem in a different way. I'm aware the sort is blocking but the data had to be sorted by a different field earlier in the flow, and as I only have a few thousand rows it's not a problem. – Tony Oct 22 '15 at 14:08
  • Sounds good glad you got it worked out. I always mention the sort, just in case. – Dave Oct 22 '15 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.