In this step by step SSIS Tutorial I will show you how to use SSIS Conditional Split Transformation. For the purpose of this tutorial I will use SSIS 2012. There are a few main changes comparing to previous version of SSIS But the principle staying the same.
VIDEO: This is text based tutorial for video tutorial see Video SSIS Sort Transformation
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server 2005 as well
In this tutorial I present Sort Transformation using SSIS 2012. I will show you how to sort based on one column and two columns.
In my first example I will show you how to sort based on One column.
WHERE [ProductKey] BETWEEN 310 AND 313
AND [OrderDateKey] BETWEEN 20050701 AND 20050705
In a Data flow I have got OLE DB Source and Conection to AdventureWorksDW2012. I duble click the OLE DB Source.
In a source I have got some SQL. I click Preview.
I can see the data I have got in a ProductKey and OrderDataKey.
I create data path and I double click the Sort
and I got Sort Transformation Editor . And in here we select the column we want to sort. InputColumn in this case is ProductKey OutputAlias is the column we will get after the sort transformation and Sort is Ascending. SortOrder is 1.
To Preview the data I will use Derived Column and Data viewer and I will Run it.
So we get preview of data sorted by product key ascending and it seems to be working.
No I check change the sort to descending an we should see the data in reveres order.
And as you can see we have the same data but in descending order.
In next example I will show you how to sort two columns. I go to Sort Transformation Editor and select OrderDateKey. Now I have got two columns ProductKey and OrderDateKey both of them will be sort on ascending but as the first one sorted will be ProductKey and then OrderDateKey.
The sorting seems to be working fine.
In this example it is quite important to know that the row order is irrelevant but the sort order is important so if we change ProductKey SortOrder to 8 and OrderDataKey to 4 we get information that "Sort order must form a monotonically increasin seaquence starting with 1."
The Sort transformation works similar like Order By. Be careful if you have certain data with different location where you need to sort it properly based on Upercase and lowercase.But in majority of cases we don't need to worry about that.
I hope that will help