Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently using Merge Join to perform an inner join on two sorted sets of data in SSIS. The problem I am having is that no records are being returned from the inner join. To troubleshoot I have performed the following:

1) I exported both sorted data sets into my local SQL server, and wrote an inner join manually. This confirmed that I should be receiving 720 rows as a result of the inner join between the two data sets.

2) I used a lookup, instead of a merge join, to perform the inner join and this actually worked. The reason I do not want to use a lookup is because of the performance I am getting with these sets of data. To give you an idea of the data I'm working with, table A has ~16k rows and table B has ~7.8 million rows. Using the lookup component yielded a run time of over an hour.

So I was wondering if you guys could help me get the Merge Join setup correctly. To help you help me, I've provided screen shots of how I currently have things configured:

(Sorry I couldn't post the full hyperlinks, stack overflow doesn't let new users post more than 2 links)

This question came from our site for professional and enthusiast programmers.

Had you, during development of the Data Flow, set the IsSorted properties on the data sources to True and provided a sort column? I know there's an issue when utilizing the IsSorted property with the Merge Join component that produces symptoms similar to yours. Sorting like you're doing with the Sort components is the ideal way to go.
–
Patrick KellyMar 6 '12 at 15:22

Normally, I'd say check your data types and or casing but if the lookup works, then that doesn't seem as probable. Safe to assume the lookup would be against the 7.8M row table b? Check your metadata anyway for the 4 keys on both inputs, maybe there's a length discrepancy or something like that?
–
billinkcMar 6 '12 at 16:21

The IsSorted property on both data sources is set to false, I was under the impression that I should only set those to true when the data is coming in pre-sorted, should I be setting it to true regardlesS?
–
David GutierrezMar 6 '12 at 17:02

Ok, I'll give all the meta data a look through. thanks for the tip!
–
David GutierrezMar 6 '12 at 17:03

I think it is indeed the meta data of one of the join columns. When I remove this column from the join condition, records begin to pass through. So I'm going to play around trying to get the type from both sources to match. Thanks again.
–
David GutierrezMar 6 '12 at 18:17

1 Answer
1

Normally, I'd say check your data types and or casing but if the lookup works, then that doesn't seem as probable. Safe to assume the lookup would be against the 7.8M row table b? Check your metadata anyway for the 4 keys on both inputs, maybe there's a length discrepancy