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?
- First it read the all the Data from Source.
- Once it read the all the data from Source then it performs Sort Transforamtion (Asc or Desc) based upon condition.
- 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.
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.
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
Once we click on “New” we will get another window. Finally click on “OK” button.
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.
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.
Clicking on “New” button will get another window.
Once again click on “New” button. We will get another window. Follow the below screen shot.
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 :-
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.
STEP 7. Performance issues with Sort transformation in SSIS :-
- It is a blocked transformation. I already mentioned few points at the starting of this article.
- It always degrades the performance So it is very bad to Implement.
- 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.
- 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.