Recently, while working on one of the existing SSIS package, we found that there were number of sort blocks in SSIS where as it could have been sorted at the source itself and informing the SSIS package that the data is already sorted at the source and can be processed further.
Another disadvantage of using Sort blocks in SSIS package itself is it loads the entire data into memory and performs sorting. Also, sorting in the SSIS package takes more time as compared to sorting at the source/SQL server for which we may get out of memory exception which will be explained in another blog.
So, to fix this issue, we sorted the data at the source using order by statement of SQL as shown:
Then, we did set the other required values by Right Click -> Show Advanced Editor
Go to Input and Output Properties Tab and set IsSorted as True as shown:
Then set SortKeyPosition as 1 for the column from Output Columns section which is included in the order by statement in the source query.
NOTE: We can set SortKeyPosition value(starting with 1) as the same order in which the columns are appearing in Order By statement in source SQL query e.g. we can set SortKeyPosition as 2 for totalsales, 3 for AvgSalesLast3Months and 4 for OrganisationId if the SQL query at source is like below:
“select .. from TableName order by Product,totalsales,AvgSalesLast3Months,OrganisationId”.
After performing all these steps, when we created a link between the OLEDB Sources and Merge Join(existing) block, we got the error saying: “The column with the sortkeyposition value of 1 is not valid. It should be 0“.
After searching over internet for this issue, we figured out that it seems to be an issue with the SSIS itself for which it’s unable to resolve the references.
To fix this, we deleted the merge join block and created one more and selected required columns from both the sources and it worked.
We followed the same approach for removing as many sort blocks as possible.
Hope it helps !!
Ajit Patra 
Thanks! Very helpful.
LikeLike