Skip to main content

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)

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

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

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

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

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

insert into EMP (FName,LName,Salary,Country)
insert into EMP (FName,LName,Salary,Country)
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)

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

Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])


{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

What is difference between UNION and UNION ALL in SQL Server

We use UNION and UNION ALL operator to combine multiple results set into one result set.
UNION operator is used to combining multiple results set into one result set but removes any duplicate rows. Basically, UNION is used to performing a DISTINCT operation across all columns in the result set. UNION operator has the extra overhead of removing duplicate rows and sorting result.
UNION ALL operator use to combine multiple results set into one result set but it does not remove any duplicate result. Actually, this does not remove duplicate rows so it is faster than the UNION operator. If you want to combine multiple results and without duplicate records then use UNION otherwise UNION ALL is better.
Following some rules for using UNION/UNION ALL operator
1.The number of the column should be the same in the query's when you want to combine them. 2.The column should be of the same data type. 3.ORDER BY clause can be applied to the overall result set not within each result set.
4.Column name of …