Tell me more ×
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.
up vote 3 down vote favorite

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)

Data Flow of Job: http://i44.tinypic.com/5xvp4z.jpg

Table A Sort: http://i40.tinypic.com/99ie4h.jpg

Table B Sort: http://i41.tinypic.com/2hdqz5t.jpg

Merge Join: http://i39.tinypic.com/xlh3s2.jpg

If there is any more information I could provide that would be helpful, please let me know!

Thanks

share|improve this question
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 Kelly Mar 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? – billinkc Mar 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 Gutierrez Mar 6 '12 at 17:02
Ok, I'll give all the meta data a look through. thanks for the tip! – David Gutierrez Mar 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 Gutierrez Mar 6 '12 at 18:17
show 1 more comment

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

1 Answer

up vote 2 down vote accepted

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

share|improve this answer
It was exactly that, a length discrepancy. Thanks! – David Gutierrez Mar 6 '12 at 20:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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