How to properly sort your source query in SSIS.

Unfortunately when you have a sorted result set in SSIS using T-SQL this sorting is not automatically picked up in the metadata. This subsequently causes additional issues with components like the Merge Join that expect the result set to be sorted and the metadata to pick this up. Because of this fact there are two additional steps that must be performed in the OLE DB Source.

    1. Add your sorted select statement to your OLE DB source.
    2. Right click and go to Show Advanced Editor.
    3. Click the last tab on top, “Input and Output Properties”.
    4. Click OLE DB Source Output
    5. Change the IsSorted property to True, the default is false.
    6. image
    7. Now expand the tree view for “OLE DB Source Output” and expand “Output Columns”
    8. Find the column that you sorted in your result set and assign it a SortKeyPosition of 1.
    9. image

Thanks for looking.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s