How to set OLE DB Source data in SSIS

Sometimes I have to learn things repeatedly before it sticks.  I re-learned this tip from Tim Costello at last night’s Oklahoma City SQL User Group presentation.  We will get his presentation up soon.

First, a big thank you to Tim Costello for coming to Oklahoma City to speak at our user group.  Check out his site for more tips.  This tip was just one tidbit from his presentation “Pro Tips: Tuning the data flow in SSIS”.   Watch for him at a SQL Saturday near you.

Why you need to set IsSorted property on the OLE DB Source

When preparing data for a Merge Transformation, the data has to be sorted.  The database sending the data can usually sort data faster than SSIS.  This works much faster if you already have data sorted in the table, but also if you use an ORDER BY clause.

The IsSorted property in the OLE DB Source informs all the downstream transformations that that data from a given data source is coming in already sorted.  I used merge joins at a project over a year ago and we really had to dig around to find these properties and get the data flow working.  I thought I better write it down on something besides post-it notes before it pops out of my head again.

NOTE:  The properties listed below do not sort the data.   See TechNet article listed below for more caveats to these properties.

How to set the IsSorted property on an OLE DB Source:

1. Start with an OLE DB Source and write a query using an ORDER BY statement to sort the data.
2. Close the OLE DB Source.
3. On the OLE DB Source, Right Click and select to the ‘Advanced Editor’.
4. Click on the ‘Inputs & Output Properties’ tab.
5. Set the ‘Data Sorted’ property to ‘True’.
6. Next we have to tell all downstream transformations how the data is sorted.
7. Expand the plus sign ‘+’ by the ‘Input Columns’
8. For each column, set the SortKeyPosition property.
   It needs both a rank and a direction.
   Use an integer to indicate the order of the list in your ORDER BY clause.
   1 for first sort column, then 2, 3, etc…
   To set the sort direction, add a plus sign ‘+’ for Ascending or a minus ‘-‘ for Descending.
9. Repeat for the ‘Output Columns’.  Use zero ‘0’ for output columns which are not part of the ORDER BY.
   For example, if the BusinessEntityID column is the first column sorted descending, we use a -1 as shown below.

Well, that is a quick photo tutorial on how to set the sort properties for an OLE DB Source in an SSIS package.  I hope this is as helpful to you as it was for me.  Again, thanks to Tim Costello.

Reference: http://technet.microsoft.com/en-us/library/ms137653.aspx