Merge Join

All,

I need to use Merge Join transformation to join two sources. One is from a PIVOT transformation and one of the output columns is ISSORTED, the other is from an OLE DB Source using a query. The Merge Join transformation requires both input source have to be sorted. I cannot find the ISSORTED property on the OLE DB Source!!

I tried to use Derived/ copy transformations but cannot find the property also. How can set the OLE query sorted in order to use the MergeJoin?

Thanks a lot

Jessie,

You have 2 options.

1) Sort the data in your query in the OLE DB Source using an ORDER BY clause. If you do this you will have to manually set IsSorted=TRUE - you do this in the Advanced Editor.

2) Use a SORT component to sort the data.

Note that setting IsSorted=TRUE does NOT sort the data for you. It only tells SSIS that the data is sorted. If you set IsSorted on a data-path that is not sorted then you will get the wrong results coming out of the MERGE JOIN.

-Jamie

|||Thank you so much Jamie|||

Hi, jamie,

This might be a dummy question.

I got the same problem as Jessie's, but when I went into the Advanced Editor of OLE DB Source, I could not find where to set up the Issorted=True. Could someone help me to find out?

TIA

|||

john zhang wrote:

Hi, jamie,

This might be a dummy question.

I got the same problem as Jessie's, but when I went into the Advanced Editor of OLE DB Source, I could not find where to set up the Issorted=True. Could someone help me to find out?

TIA

It is a property of the output. OLE DB Source only has one output (plus the error output) so its not too difficult to find.

-Jamie