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.8/10 (36 votes cast)
VN:F [1.9.22_1171]
Rating: +32 (from 32 votes)
Sort Transformation in SSIS, 9.8 out of 10 based on 36 ratings

60 COMMENTS

  1. 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.

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

    • 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

  3. 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

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

  5. 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

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

    • 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

  7. 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

  8. 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

  9. 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

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

Comments are closed.