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)
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
  

Popular posts from this blog

Merge and Merge join transformation in SSIS

MERGE TRANSFORMATION
Using Merge Transformation we can combine two sorted data-set into single data-set basically Merge Transformation used to combines rows from two sorted data flows into one sorted data flow. Following tasks you may perform using Merge Transformation: 1.Suppose we have a scenario like, we need to merge data from a database table and excel means we want to merge data from two different data sources. For such type of scenario, you can use Merge Transformation. 2.If we want to merge data from two same structured tables but exists two different servers. 3.Sometimes we get an error due to data in a row, after correcting errors in the data we can re-merge rows easily. See below explanations may help you to understand Merge Transformation: I do evaluate here, you already know about the data source, data conversion, data flow, task flow, control flow etc. Note:Before Merge transformation, we need to sort the data using Sort Transformation. After sorting data add data path to Merge…

Remove special characters from string in SQL server

I faced many times an issue to remove special characters from a string. Suppose you are working on searching concept and you have to remove the special characters from search string due to query performance, there are many solution are available but T-SQL is easily resolved this issue.
Following query may help you to resolve your issue.

DECLARE@strVARCHAR(400) DECLARE@expresVARCHAR(50)='%[~,@,#,$,%,&,*,(,),.,!]%' SET@str='(remove) ~special~ *characters. from string in sql!' WHILEPATINDEX(@expres,@str)> 0 BEGIN SET@str=Replace(REPLACE(@str,SUBSTRING(@str,PATINDEX(@expres,@str), 1 ),''),'-',' ') END SELECT@str



Drop a database in PostgreSQL

In this article we will learn how to drop a database in PostgreSQL, we will learn how to drop a database using SQL command and using pgAdmin4 as well. Be careful before dropping a database because once you dropped the database you will lose all the complete information stored in the database.
Using DROP DATABASE SQL Command in PostgreSQLUsing this command, we can drop the database. Once you execute this SQL command, you will lose all your data, database structure, tables, schemas, etc. So be careful before dropping any database, make sure that you are leaving the correct database, I suggest that you have to take the backup before leaving the database.

DROPDATABASEDatabase_Name

Using pgAdmin4 drop databaseRight click on database-> select Delete/Drop