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

Question

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:

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

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

Answer 1

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.

Answer 2

The number of rows shouldn't be what you're using to gauge if you're using the correct options for the Merge Join. The resulting data set should be the driving factor. Do the results look correct in the tmpManAddress table?

For development you might want to push the output of the script components to tables so you can see what data you're starting with. This will allow you to work out which type of join, and on which columns, give you the results you want.