2

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).

Sam Hosseini
  • 813
  • 2
  • 9
  • 17
JFV
  • 1,803
  • 6
  • 24
  • 38
  • This is more of a database question so post sample table structure, sample data, expected results. Throwing up a nice SSIS graph / visio graph is tres beautiful but totally not helpful to resolve your issues. – JonH Mar 08 '11 at 20:04

2 Answers2

2

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.

grapefruitmoon
  • 2,988
  • 1
  • 22
  • 26
  • I appreciate the answer, but I'm not sure how to implement it. I was able to get around it by doing the manipulations in the dataflow and dumping the values in the 2 tables, then using an Execute SQL Task to run an UPDATE script to add the column values into the destination table. – JFV Mar 10 '11 at 17:54
0

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.

Ben L.
  • 121
  • 1
  • 4