November 5, 2009 at 10:56 am
I have two OLEDB data sources that I want to join together. Both sources use a SQL statement to get the data and sort it (order by). I have set the IsSorted property of both OLEDB sources to True and set the SortKeyPositions correctly. The Merge Join component sees that everything is order and I do my inner join selecting all the columns from Source A and the needed column from Source B. Of the the 300K records that go through the Merge Join only 400 are getting joined. I double checked the Order By in both SQL statements and they match. The SortKeyPositions are the same.
Just to make sure that I wasn't going completely crazy I put a sort on each source. I sorted by the same columns. (SSIS was nice enough to let me know that I don't need to use the sort as the data were already sorted.) When I run the package now I am getting my 300K joins. :w00t:
Just to make more of my hair go gray, there is another data flow task in the same package that uses the SQL statement in the source to order the records before doing a merge join and that works perfectly.
I'm open to suggestions.
November 5, 2009 at 11:36 pm
This is a common behavior for which the only solution, I have been able to get to work or seen posted on the net as a solution, is to use the Sort transformation. I have found it to be safest to just add that transformation to packages when i need to use a merge join.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply