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 (35 votes cast)
VN:F [1.9.22_1171]
Rating: +31 (from 31 votes)
Sort Transformation in SSIS, 9.7 out of 10 based on 35 ratings
  • anilkumar

    please give send me MDX and cube notes

  • Hai Anil,
    Thanks for visiting PhpRing

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

    Thanks

  • sanjiv

    very awsome sort transformation

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

      Thanks,
      Avinash

  • upendra

    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.

    • 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

      • upendra

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

        upendra.ratti@gmail.com

        • 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

  • Chigicherla Mallikarjuna

    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.

    • HI Malli,
      Welcome to PhpRing. very glad u liked this article. I will forward some pdf’s regarding MSBI soon.

      Thanks,
      Avinash

      • Chigicherla Mallikarjuna

        HI Sir,
        Thanks for sending MSBI And SQL Documents……….

        Thanks And Regards,
        C.Mallikarjuna……..

  • Sathish

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

  • Sathish

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

    • 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

      • Madhu

        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.

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

      Thanks,
      Avinash

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

    Thanks,
    Chander Sharma

    • Avi

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

      Thanks,
      Avi

  • Swamy

    Very nice articles. Thanks for posting such a wonderful articles

  • Pavan

    Hi
    Nice article and expecting more on SQL

  • Pavan

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

    Thanks in Advance

    • Hi Pavan,

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

      Thanks,
      ck

  • lavandeep garg

    Thanks for your efforts

  • samba siva rao

    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

    • 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

  • Ram Ganachari

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

    • 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

  • Ysn Murthy

    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

    • 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

  • Avinash Reddy

    Hi Avinash,

    Hope u r doing well!!
    can u please send me the ssis , ssrs material.
    Thanks
    Avi

  • manikanta

    Hi Avinash …
    Thanks alot for this good explanation.
    Could you please tell me what are the naming conversions between DataStage, Informatica and SSIS

    • Avi

      Hi Manikanta,

      I’m glad to know that you liked my explanation.

      Coming to your question..I never work with informatica and Datastage, I’m unable to provide answer for this.

      Thanks,
      Avi

  • Suresh Areti

    Hi Avinash Sir,

    I don’t have any idea of MSBI . I know SQL SERVER I want to learn MSBI Please guide me and please send me material.My mailid is sureshareti1@gmail.com

    • Hello Suresh,

      Apologies for late reply.
      I’ll send you the material tomorrow morning.

      Thanks!
      Avi

  • nagaraj

    hi everyone ,
    I been going through the PHPRING site regularly, its very interesting and easy to learn process as you are providing simple appoaches with screen shot.
    Hope you people continue this with more stuffs.
    Can you people update with some real time scenarios on SSIS package and SQL server topics like stored procedures , so that it will be helpful for the many people.
    Can you please share any docs to my mailid nagraj275@gmail.com on real time scenarios on SSIS package and SQL server topics.
    Thank you so much guys #PhpRing #MSBI

    • Hello Nagaraj,

      We are very glad to hear that you are regularly visiting #PhpRing.
      I hope you liked our efforts.
      I’ll send you the material soon.

      Thanks for your support.

      Thanks!

      Avi

  • Pingback: ssis tutorial pdf - computer tutorials()

  • Gangaraju

    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 :- nichenakollagangaraju@gmail.com

  • Hey Gangaraju,

    I’m glad to see your comment.
    I’ll send you the material soon. Stay tune to Phpring !!!

    Thanks!
    Avi

  • Laxmi

    Awesome explanation. Can you please update with some real time scenarios on SSIS package and SQL server topics like stored procedures , so that it will be helpful for the many people.
    Can you please share any docs related to MSBI(SSIS,SSAS,SSRS)and SQL Server to my mail_id msbicrackers@gmail.com on real time scenarios on SSIS package and SQL server topics.
    Thank you so much guys #PhpRing #MSBI

  • srinivas.ch

    Hi Chander Sharma,
    It’s good explanation and I understood very well.
    Thanks bro.

    • Hello Srinivas,
      Thanks a lot for your feedback. Stick with us for more in future

      Thanks,
      Avi

  • Prathima

    Hi Avinash,
    I recently started learning MSBI.I have gone through your website it was very interesting.
    Can u please send me the docs on MSBI and sql to my mail-id prathima.sandra11@Gmail.com
    Thanks

    • Hello Prathima,
      I’m glad to know that you liked my tip and our Team efforts.
      Thanks for your feedback and I’ll send you the material on tomorrow.

      Thanks,
      Avi

  • Sreeram

    Hi Avinash,

    I went through your blog and I am happy that you are sharing your views in online which are helping others directly or indirectly which is a great job. I am learning MSBI and SQL server, could you please mail me the necessary documents.
    Hope you people will continue the same and once again Thanks for your efforts

    • Hey Sreeram,

      I’m very happy to hear such kind of nice words from you.

      We will continue to frame our thoughts in the form of articles in future too.

      Coming to your question—-please share your gmail id. I can share doc’s

      Thanks!

      Avi

  • karuna reddy

    Hi
    very nice article…

    I need SQL Documents and MSBI Documents please can u forward to me krish.pakala@gmail.com

    • Thanks for liking our efforts!!

      I’ll send you the doc’s soon

      Thanks!
      Avi

      • HI Avinash,

        If you have any documents for sql and SSIS.Can you send me the documents.

        Mail ID: mareddy.ani@gmail.com

        Thank you.
        Anitha

  • Bhaskar

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

  • bhaskar

    hai sir,
    nice article…I need SQL Documents and MSBI Documents please can u forward to me.
    mail id:: av.bhaskar88@gmail.com.

    thank you sir

  • Bhim

    Hi Sir,Excellent article. Keep it up.. I need realtime SQL queries Documents and ETL Testing Documents please can u forward to my mail id.
    mail id:: koribp@gmail.com