The Trouble with SSIS Sorting

Sort it all out, but sort it the right or there'll be trouble, of a sort.

Many SSIS transformations, such as the Pivot and the Data Profiling Tasks, require that the data being fed into them be sorted. Without first sorting, some transformations will throw an error and not run, while others will run but the results will not be as expected.

The answer is to sort the data before it is fed into the transformation; the problem is that this isn't as simple as you may think.

The obvious and simple way to get data that's sorted correctly is to use the Sort Transformation in SSIS. The odd thing is that the Sort Transformation will complete the objective of sorting the data, but the performance might not be exactly what you would expect. The Sort Transformation does all its work in memory; SSIS packages do things in individual sets. This means your data may need to be sorted and re-sorted again. If you try to work with large data sets, this sorting will take a long time and use a lot of memory.

The solution is to sort the data from the source using an ORDER BY clause. That alone, however, won't get it done. You also have to tell SSIS that the data is sorted and how it is sorted. This is done in the Advanced Editor of your data source.

First, set the IsSorted property of the source's output to true. Next, set the SortKeyPosition property of each column you specified in your ORDER BY clause; 1 for first, 2 for second, and so on. A SortKeyPosition value of 0 is the default and means that the column is not used in the sort.

That's all there is to it! Using this method, you will be able to sort your data so the SSIS transformations are happy, and can work without hours of waiting and memory hogging.

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at www.consortioservices.com.

Featured

  • RAMBleed Side-Channel Attack Method Disclosed by Researchers

    Academic researchers this week published information about another side-channel attack method, called "RAMBleed," that can expose information from memory chips, including encryption key information.

  • Penguin

    Windows 10 Preview Build 18917 Shows Off New Linux Integration

    Microsoft's latest Windows 10 "fast-ring" preview release is showcasing a coming Delivery Optimization enhancement, along with the ability to try the newly emerged Windows Subsystem for Linux version 2.

  • Customizing Microsoft Office 365

    While the overall look and feel of Office 365 is pretty standard across organizations, there are several ways to personalize it and make it fit better with your company's specific needs.

  • Microsoft 365 Business Tenants Getting Conditional Access and Trouble-Ticket Features

    Microsoft added its conditional access security service to Microsoft 365 Business subscriptions, according to a Wednesday announcement, and it also added new trouble-ticket features for Microsoft 365 administrators.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.