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

Comments

  1. By anilkumar

    Reply

  2. Reply

  3. By sanjiv

    Reply

    • Reply

  4. By upendra

    Reply

    • Reply

      • By upendra

        Reply

        • Reply

  5. By Chigicherla Mallikarjuna

    Reply

    • Reply

      • By Chigicherla Mallikarjuna

        Reply

  6. By Sathish

    Reply

  7. By Sathish

    Reply

    • Reply

      • By Madhu

        Reply

  8. By Sathish

    Reply

    • Reply

  9. Reply

    • By Avi

      Reply

  10. By Swamy

    Reply

  11. By Pavan

    Reply

  12. By Pavan

    Reply

    • Reply

  13. By lavandeep garg

    Reply

  14. By samba siva rao

    Reply

    • Reply

  15. By Ram Ganachari

    Reply

    • Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Current ye@r *