Blog

Avinash Reddy Munnangi
Jan 4 th, 2014
SSIS36 Comments

Sort Transformation in SSIS

Aim :- In this, we will implement Sort Transformation in SSIS. In our earlier SSIS Tutorials, we discussed Control flow in SSIS. But, with this post we will now move forward to Data flow in SSIS. Sort Transformation is present in Data Flow tab. We will now learn the functionality of Sort transformation in SSIS.

Description :- Sort can be defined as any process of arranging items or data according to a certain sequence. Sort transformation in SSIS works on the same principle. Sort transformation in SSIS is used to “Sort” the data either ascending or descending order. Sort transformation in SSIS is mainly used with Merge or Merge Join Transformations. We can call Sort transformation as Asynchronous Transformation and Blocked Transformation. Are you wondering what is Asynchronous and Blocked transformations? We have a solution right below this.

What is a Blocked Transformation?

Blocked Transformation means – The next operation can’t start until & unless it completes the previous operation. In this, rows will not be send to next transformations until all of the Input rows have been read. In simple words, they will process all rows first and than release them for further Transforamtions.

How it works?

  1. First it read the all the Data from Source.
  2. Once it read the all the data from Source then it performs Sort Transforamtion (Asc or Desc) based upon condition.
  3. Finally it loads the Data into destination.

I think this explanation is enough to understand the concept of Blocked transformations. Now we are good to learn Sort Transformation in SSIS. Let’s work on Sort transformation in SSIS with a simple example.

STEP 1.  Source Data :-

Below is a file containing my source data. It contains data with few duplicate rows as well.

Sort transformation in SSIS_Source Data containing Some duplicate rows

STEP 2.  Business Intelligence Development Studio – BIDS :-

  • Open BIDS, Create one SSIS project. Here my Project name is PHPRING.
  • In this project, create one Package and name it as say “Sort”.
  • Drag and drop one Data Flow Task (DFT) to Control Flow.
  • Double click on Dataflow Task. Then drag and drop – Flatfile Source, Sort transformation and OLEDB Destination and connect them all.
  • Here, my source is in File format- That’s why I am taking source as Flatfile. And I want to load my Source data into SQL table – That’s why I am taking destination as oledb.

Follow below screen shot if you have any doubt while taking Source, Sort transformation and Destination.

Sort transformation in SSIS_Sort Transformation

STEP 3.  Configuring Flatfile Source :-

Double click on Flatfile source to set properties. Once you double click on Flat file source, the below wizard will appear. Click on New button

Sort transformation in SSIS_Flat file Source editor

Once we click on “New” we will get another window. Finally click on “OK” button.

Sort transformation in SSIS_Flat file Connection Manager Editor

STEP 4.  Configuring Sort Transformation in SSIS :-

It’s time to Configure Sort transformation in SSIS. Double clicking on Sort transformation will pop up the below window. Now, set the properties like done below.

  • Check column “name” in the available columns.
  • Give Sort order as Ascending.
  • Check the box below ( If you want to Remove rows with duplicate sort values).
  • Finally click on “OK” button.

Sort transformation in SSIS_Apply Sort Transformation

So, till here we complete the configuration of Flatfile Source and Sort transformation in SSIS. Finally, we can complete this with the configuration of OLEDB Destination.

STEP 5.  Configuring OLEDB Destination :-

Double click on OLEDB Destination. The below window will appear on our Screen.

Sort transformation in SSIS_OLEDB Destination Editor before Sort transformation

Clicking on “New” button will get another window.

Sort transformation in SSIS_Configure OLEDB Connection Manager

Once again click on “New” button. We will get another window. Follow the below screen shot.

Sort transformation in SSIS_Configure New OLEDB Connection Manager

Finally click on “OK” button. So guys I configured everything, it’s time to run our package and observe the output.

STEP 6. Executing our Sort Transformation in SSIS Package :-

Sort transformation in SSIS_Executing SSIS Package

In this screen shot we can clearly observe that 12 rows are coming from Source. But only 8 rows are loaded into SQL table “Sort”.

Between Source and Destination, we have Sort Transformation. So it eliminates all the duplicate rows as we check the box above in STEP 4. That’s why we got only 8 rows as Output.

 

 

Now, I am switching to my MS Sql Server to check table (sort). My output data is stored in this SQL table. In the below screen shot, we can clearly observe the output (8 rows). Also, we can see the Sorted column “ename” in Ascending order.

Sort transformation in SSIS_Output in SSMS

STEP 7.  Performance issues with Sort transformation in SSIS :-

  1. It is a blocked transformation. I already mentioned few points at the starting of this article.
  2. It always degrades the performance So it is very bad to Implement.
  3. When a blocking transformation is encountered in the Data flow, a new buffer is created for its output and a new thread is introduced into the Data flow.
  4. Instead of using Sort transformation in SSIS, It is better to Sort out data with Tsql (Order by clause).

With this we complete our article on Sort transformations in SSIS. I hope you like it. Please provide your feedback and comments below.

 

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.7/10 (23 votes cast)
VN:F [1.9.22_1171]
Rating: +22 (from 22 votes)
Sort Transformation in SSIS, 9.7 out of 10 based on 23 ratings

36 Comments on this article

  • anilkumar January 7, 2014

    please give send me MDX and cube notes

    Reply

  • Avinash Reddy Munnangi January 12, 2014

    Hai Anil,
    Thanks for visiting PhpRing

    please drop your mail id here ,i will forward few docs to u .

    Thanks

    Reply

  • sanjiv January 20, 2014

    very awsome sort transformation

    Reply

    • Avinash Reddy Munnangi January 22, 2014

      Hi Sanjiv,
      Thanks for visiting PhpRing and also providing feedback to this article, I am very glad u liked our effort.

      Thanks,
      Avinash

      Reply

  • upendra January 24, 2014

    Very Nice Explanation.I am .net Programmer Where i touch with .net technologies and sqlserver.I would Like learn about MSBI(SSIS,SSAS,SSRS).Please Forward the material Which is Useful for My later development in .net Technology.

    Reply

    • Avinash Reddy Munnangi January 31, 2014

      Hi Upendra,
      very glad you liked it.

      I am not aware of .net, please drop your mail id here i will send you some material related to MSBI

      Reply

      • upendra February 25, 2014

        Sorry Mr.Avinash,being late of my reply. Please Forward Material to mail:

        upendra.ratti@gmail.com

        Reply

        • Avinash Reddy May 27, 2014

          Hi Upendra,
          Sorry for the late reply, i was not that much active on blogging and facebook now a days.
          I’m back here and will start to work for PhpRing. I will send you the material soon.

          Thanks,
          Avi

          Reply

  • Chigicherla Mallikarjuna February 5, 2014

    Nice Article,

    Very Nice Explanation.I would Like learn about MSBI(SSIS,SSAS,SSRS).Please Forward the material Which is Useful for Us.

    Plz forward MSBI material to me :- malli.helps@gmail.com.

    Reply

  • Sathish February 10, 2014

    Very nice article.I am doing migration from DTS to SSIS and Reporting as well.Can you plz share the related doc’s.
    Thanks

    Reply

  • Sathish February 12, 2014

    thank you very much Avinash for your prompt and courteous response.

    Reply

    • Avinash Reddy Munnangi February 13, 2014

      You are welcome my friend.

      Its my duty to respond each and every person who post their comments here . I am very glad to see the folks comments , its my pleasure to hear they liked PHPRING and also my article.

      Thanks a lot for your valuable feebbacks. If i find any doc’s related to Migration, I will forward to your mail id Sathish.

      Thanks,
      Avinash

      Reply

      • Madhu September 24, 2014

        Hi Avinash,
        This is madhu, i want msbi and sql document. can u please send me my email @
        —– madhua333@gmail.com —–
        Your articel is very usefull.

        Reply

    • Avinash Reddy Munnangi February 26, 2014

      Hi Sathish,
      I will send you if i find any material regarding your requirement.

      Thanks,
      Avinash

      Reply

  • Chander Sharma May 21, 2014

    Hi Avinash,
    I found this article on Sort Transformation worth. You did a great job. Keep up the brilliant work bro.

    Thanks,
    Chander Sharma

    Reply

    • Avi May 27, 2014

      Hi Bro,
      very glad to see your comment, thanks for your tremendous support.

      Thanks,
      Avi

      Reply

  • Swamy July 5, 2014

    Very nice articles. Thanks for posting such a wonderful articles

    Reply

  • Pavan July 8, 2014

    Hi
    Nice article and expecting more on SQL

    Reply

  • Pavan July 8, 2014

    I need SQL Documents and MSBI Documents please can u forward them to me

    Thanks in Advance

    Reply

    • Chander Sharma July 14, 2014

      Hi Pavan,

      Accept apology for replying late. We are sending right away into your mailbox !!! :)

      Thanks,
      ck

      Reply

  • lavandeep garg October 20, 2014

    Thanks for your efforts

    Reply

  • samba siva rao November 5, 2014

    Hi sir
    i have read your posts
    Very Nice Explanation
    I would Like learn about MSBI(SSIS,SSAS,SSRS).Please Forward the material Which is Useful for Us.
    please send me MDX and cube notes
    and Documents……….
    mail id: peddisetty.rao@gmail.com

    Reply

    • Avinash Reddy Munnangi November 8, 2014

      Hi Samba,

      I’m very glad you liked my tips.

      Sure, I’ll send you the doc’s ASAP. please stick for more tips.

      Thanks,
      Avi

      Reply

  • Ram Ganachari November 14, 2014

    Hi,
    Very helpful for the beginers of SSIS. I want to learn more on this SSIS. Please share the relevant docs.
    Thanks in advance

    Reply

    • Avinash Reddy Munnangi November 16, 2014

      Hi Ram,

      Thanks for your valuable feed back.
      Sure we will line up andpost more articles on SSIS on daily basics.
      Pease stick for more.

      Thanks,
      Avi

      Reply

  • Ysn Murthy January 2, 2015

    Sir, I am learning SSIS since 15 days and I am very much impressed with your material and very easy and interesting.
    I am requesting you to please drop the ssis,ssrs material to my mail id: murthy_ysn@yahoo.com

    Thanks,Murthy

    Reply

    • Avinash Reddy Munnangi January 9, 2015

      Hi Murthy,

      Glad to hear your words.

      I’ve noted your mail id and will shoot you mail with the doc’s soon.

      Thanks,
      Avi

      Reply

Add a comment