why no data returned after merge join?
Hello,
It's my first time to use merge join since lookup transformation does not meet my request. I followed exactly to set sort for both 2 sources (OLE DB Source), after sort, I have merge join to set as inner join, and selected required columns. Then I will transfer
this merge join transformation data to OLE DB Destination table.
My question is: from TSQL inner join, it should return over 10,000 records, but from merge join, it returned nothing.
Could anybody tell me what's wrong with my SSIS? I am using SQL Server 2005 SSIS.
Thanks in advance!
December 20th, 2010 4:00pm
Hi,
If are getting rows sorted from source you must take care with collations.
Try to order using Order component on dataflow before merge both pipelines.Vctor M. Snchez Garca (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky
Free Windows Admin Tool Kit Click here and download it now
December 20th, 2010 4:16pm
Thanks for the response!
I did select Sort Transformation tool to sort both 2 sources. I can see that all data go to merge join transformation, but not insert into destination at all. It runs successfully.
December 20th, 2010 4:22pm
I followed the link below to try my merge join. The only difference is I used char as my sort column. Will it be the problem?
http://www.ssistutorial.com/create_join_between_datasets_in_ssis.php
Free Windows Admin Tool Kit Click here and download it now
December 20th, 2010 4:31pm
You may have some sort of issue preventing the data set to be properly sort. I don't understand what you mean by 'I sued char as my sort column', but in general teh data type and sort specs needs in the 2 inputs need to match. btw, Merge join have several
limitations that make a bad choice in most scenarios (performance, troubleshooting). If the 2 data sets are coming from the same data base I would use a single source component with a query to join them. If there are in different data bases or come from
non-relational sources, then I would typically stage the data in tables and then use a single query to bring them together via join.Help Others! Don't forget to mark your thread as answered
December 20th, 2010 11:18pm
Order by a char column dont would be a problem if both sources have same datatype and lenght. Anyway, try to set the Merge Join to Left Join instead Inner Join and see results. If with this configuration the component drop rows, then there are some differences
in your data that not generates matches. Add a data viewer over the pipe after Merge Join to review the columns content.
Regards.Vctor M. Snchez Garca (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky
Free Windows Admin Tool Kit Click here and download it now
December 21st, 2010 3:11am