Ordering OLEDB Source Adapter Output
There are a couple approaches to ordering the rows delivered by an SSIS OLEDB source adapter.
First, open an SSIS package and add a Data Flow Task to the Control Flow. Open the Data Flow to proceed.
Approach 1 - Inline sorting using the Sort transformation
Configure the OLEDB source adapter to return all rows from a table:
Add a Sort transformation to the data flow and connect the OleDB source adapter to it as shown:
Double-click the Sort transformation to edit it. Select the fields you wish to sort on, in order, and specify ascending or descending sort order:
The problem with this approach is it can slow things down quite a bit if there's lots of data in the table you're sorting.
Approach 2 - Load the data sorted from the source
Configure the OLEDB source to use a SQL Command. Include the desired sort order in the Order By clause as shown:
Click OK to close the OLEDB source adapter editor.
Right-click the OLE DB source adapter and click "Show Advanced Editor...":
Navigate to the Input and Output Properties tab. Click on "OLE DB Source Output" and set the IsSorted property to True as shown:
Next, expand the "OLE DB Source Output" node and the "Output Columns" folder beneath, and click on the desired sort columns.
Set the SortKeyPosition property to indicate both direction and sort order. Direction - ascending or descending - is indicated by the sign of the number (negative = descending, positive = ascending). Sort order is indicated by the value of the number.
Therefore, a SortKeyPosition of -1 indicates [ExpYear] is the first field listed in the Order By clause, and is sorted descending:
Similarly, a SortKeyPosition of 2 indicates [ExpMonth] is the second field listed in the Order By clause, and is sorted ascending:
This method is preferable over the first for performance and scalability.
:{> Andy
Technorati Tags: SSIS Order By Ole DB Sort