3

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 screenshots of how I currently have things configured:

Data Flow of Job

Data Flow of Job

Table A Sort

Table A Sort

Table B Sort

Table B Sort

Merge Join

Merge Join

migrated from stackoverflow.com Mar 6 '12 at 17:22

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

4

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

  • 1
    It was exactly that, a length discrepancy. Thanks! – David Gutierrez Mar 6 '12 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.