Not recognising the sort on a Merge Join.

Question

Not recognising the sort on a Merge Join.

Ells

SSCrazy Eights

Points: 9901
More actions
November 15, 2009 at 3:35 pm

#140033

I have two OLE DB data sources. Both of these are f;agged as having sorted output columns. The one OLE DB data source connects to the Merge join task and is ok. The other data source goes through two data conversion and a derived column. This then errors when connected to the merge join.
Does something need to be done in the two data conversions or the derived column?
Cheers.
Ells
😎

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I believe that once you have passed the data through some transforms that SSIS no longer considers the input to the MERGE JOIN as sorted, the transform just before the MERGE JOIN needs to be sorted.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

This is a common occurrence in SSIS. Even if the isSorted property is set and the SortOrder defined in your source, merge joins sometimes ignore them. For the merge join to work best, I have found it necessary to place a "SORT" transformation task from each source right before the 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

Ells SSCrazy Eights Points: 9901 More actions · Answer 3

Thanks gents,

bit disappointing. I was hoping deep down that I had done something to cause the behaviour.

Thanks.

Ells

😎