SSIS: Order by Column Full Result Set for Execute SQL Task

Question

I have an Execute SQL Task placed before a Foreach Loop Container so that for every row returned by the Full Result Set a Script Tasks Inserts this into an Excel row.

However, on one particular column I would like to sort the Full Result Set in ASC order before iterating though each row so that I can have the Full Result Set in a particular order.

I've tried using the Order By Clause in the SQL Script inside the Execute SQL Task but it doesn't sort as expected.

Is there a way I can sort the query results in the Result Set object in order by a single column before passing onto another process i.e. the Script Task?

Answer 1

As it seems you've learned, you cannot use ORDER BY in an Execute SQL task. Instead, use a Sort task to order the data after it's been loaded from the Execute SQL task.

Using the Sort Transformation Editor you can choose what columns are sorted and how you want to view them, in addition to determining "pass-through" columns or removing them from the Data Flow.

This image uses an OLE DB Source, but the actual effect of the Sort task is the same if you use an Execute SQL task

This does pose the question: Why are use using an execute SQL Task? An OLE DB source would be much easier and more flexible to use. Consider rewriting your SSIS package to use OLE DB data sources when possible.