Learn how to a build a cloud-first strategyRegister Now


In SSIS, is it possible to sort the data without a Sort transformation?

Posted on 2011-10-21
Medium Priority
Last Modified: 2012-05-12
I heard it is possible to to sort data from a data source without using a Sort Transformation.  I'm using ADO.NET Data Source.  It should have a sorting property if we use a SELECT statement with an ORDER BY, but I cannot find it anywhere.  Any idea?  Thanks.
Question by:thomaszhwang
LVL 37

Accepted Solution

ValentinoV earned 2000 total points
ID: 37010941
When you use the ORDER BY clause, you do indeed need to tell the source component that the incoming data is sorted.  This is done by opening up the Advanced Editor (right-click on source and select Advanced Editor), then in the Input and Output Properties, locate the IsSorted property for your "source output" and set it to True.

For each column on which you've sorted, you also need to set the SortKeyPosition.

I've explained this in more detail in one of my articles, found here: http://blog.hoegaerden.be/2010/08/26/calculating-lastxmonths-aggregations-using-t-sql-and-ssis/
(look for the "Setting up the data sources" title)

Hope you can get it to work with this info!

Author Closing Comment

ID: 37011346

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Loops Section Overview

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question