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
Good work Dilip !
ReplyDeleteYou tried to explain in a very easy language with snap-shots, that make some differences.