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.