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

Posted on 2011-10-21
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

    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:
    (look for the "Setting up the data sources" title)

    Hope you can get it to work with this info!

    Author Closing Comment


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    In this article I will describe the Backup & Restore 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.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now