Sort Transformation in ssis

Sort transformation responsible to arrange data in ascending or descending order and copies the data to the transformation output. Multiple sorts can be apply to an input**. The Sort transformation can also remove duplicate rows which is part of its sort.

For Example

Create a EMP table as bellow

Create table EMP(

Id int identity(1,1),

FName VARCHAR(50),

LName VARCHAR(50),

Salary DECIMAL(18,2),

Country VARCHAR(50)

)

insert into EMP (FName,LName,Salary,Country)

Values('Dilip','Singh',20000,'IND')

insert into EMP (FName,LName,Salary,Country)

Values('Viru','Verma',40000,'USA')

insert into EMP (FName,LName,Salary,Country)

Values('Anil','Singh',30000,'UK')

insert into EMP (FName,LName,Salary,Country)

Values('Raju','Shukla',25000,'IND')

insert into EMP (FName,LName,Salary,Country)

Values('Raj','Kushwaha',20000,'AUS')

insert into EMP (FName,LName,Salary,Country)

Values('Salim','Sekh',26000,'PAK')

insert into EMP (FName,LName,Salary,Country)

Values('Nirmit','Katihar',27000,'AUS')

insert into EMP (FName,LName,Salary,Country)

Values('Dilip','Singh',22000,'USA')

First Select OLE DB data source from data flow sources and drag and drop it in the data flow then Double click on the OLE DB data source to open a new window where we can set the properties of the connection and Select the connection manager and click on new button to set the connection string.

Now Drag and drop sort transformation on Data Flow Task and provide connection between  Source and Sort using Data Flow Path.

Edit Sort, it will open a window as bellow

 
Select the column names on which you want to sort the data and You can use Sort Type option to SORT the data either in Ascending or in Descending order. you can choose more than one column for sorting.

Before Sorting data looks like bellow...

And After sorting(descending) over Column id looks like bellow

I am going to apply sorting (Ascending)  on column FName the data look like bellow

you can remove row with duplicate sort value just checked the option as sown in 2nd pic.

I adds arecords

insert into EMP (FName,LName,Salary,Country)

Values('Dilip','Singh',23000,'AUS')

which FName and LName is same but Country is different and try to sort on two columns FName and Country let us see what result comes.
 

 

You will see here it sort applied first on Fname and then on Country. Please concentrate on marks data.

** Because each sort is identified by a numeral that determines the sort order. The column with the lowest number is sorted first, the sort column with the second lowest number is sorted next, and so on..

please see bellow pic.

NOTE: The Sort transformation does not sort GUIDs in the same order as the ORDER BY clause does in Transact-SQL. While the Sort transformation sorts GUIDs that start with 0-9 before GUIDs that start with A-F, the ORDER BY clause, as implemented in the SQL Server Database Engine, sorts them differently