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.

Yes, the data is sorted, that's why i'm not adding the sort component. — user3140982
– user3140982, Commented Jan 5, 2014 at 5:38

steoleary · Accepted Answer · 2014-01-04 19:51:03Z

3

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

answered Jan 4, 2014 at 19:51

steoleary

9,3682 gold badges38 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3140982 Over a year ago

The excel data is sorted, so I only need to let the merge component know that

Pedro S Cord · Accepted Answer · 2015-09-18 14:24:25Z

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 ...

answered Sep 18, 2015 at 14:24

Pedro S Cord

1,36917 silver badges20 bronze badges

Comments

user3140982 · Accepted Answer · 2014-01-07 19:41:18Z

0

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!

answered Jan 7, 2014 at 19:41

user3140982

1351 gold badge3 silver badges8 bronze badges

Collectives™ on Stack Overflow

SSIS - set script component output IsSorted property to true

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related