Friday 3 August 2012

SSIS Merge Join fails with indeterminate results

With an SSIS Merge join, the ordering of the columns on which the join takes place is critical. Get them wrong - by defining the IsSorted property to true on the data source and setting the wrong SortKey properties - and you get interderminate results. These are the worst kind - there are no errors and your join fails occassionally (depending upon the data being operated upon).

The safest thing to do is to manually sort the columns within the SSIS package using the Sort task. Sure, it may consume some additional CPU cycles, but it'll save you a world of pain when some data is (or isn't) being returned from the merge output when you least expected it.

No comments:

Post a Comment