sql-server-2005ssisssis-2005

SSIS 2005 Can Merge Join accommodate one-to-many joins

I have a Data Flow Task that does some script component tasks, sorts, then does a Merge Join. I'd like to have the Merge Join do the join as a 1-many. If I do an Inner Join, I get too few records:Too Few Records

If I do a Left Outer Join, I get WAY too many records: WAY too many!

I'm looking for the Goldilocks version of 'Just Right' (which would be 39240 records).

Solution

  • You can add a Conditional Split after your left join version of the Merge Join, with a non-matching condition like

    isnull(tmpAddressColumn)

    and send the relevant matching flow condition (the default output) to your destination.

    If you still don't get the correct number, you'll need to check the merge join conditions and check if there are duplicate IDs in each source.