SSIS - set script component output IsSorted property to true

Question

I have 2 excel sources the data flow, on the excel source I have IsSorted property set to true and the SortKeyPosition set to 1 on one of the columns, each flow goes into a synchronous script component that assigns values to some of the columns then both outputs go to a merge join task.

the merge component complains because the inputs are not sorted, when I look at the metadata of the outputs from the script component I can see the sortKeyPosition set for the column set to 1 so i'm thinking it could be the IsSorted property for the output being reset in the script task, is there a way to set it to true?

I tried advanced editor but no luck.

Answer 1

Your data must actually be physically sorted for the merge to work, just setting the is sorted property doesn't actually sort the data it just provides a hint to the downstream components that it has been sorted.

You can fix this by placing a sort operator between the two components which will ensure that the data is in fact sorted correctly. Have a look at the following for more detail:

Sort Data for the Merge and Merge Join Transformations

Answer 2

This may helps http://liguoliang.com/2012/ssis-set-issorted-property-for-the-source-of-mergejoin/

Both source have to be sorted by the same column. - right click on the source - go to advanced editor - set the source IsSorted property to True - set the columns sorted position to 1, then 2, then 3 ...

Answer 3

I was able to find out the issue, the column that had the SortKeyPosition set to 1 was added as a read/write column to the script component, when I removed it from the input columns of the script component the merge join component recognized the input as sorted!