I'm learning about sort transforamtion. I gave a sql statement which has an order by clause (this is the sql stmt :select orderid,firstname,lastname,price from orders order by price,firstname) in it as an input to a sort transformation task. the resulting o/p is placed in a table. ie) from an ole db source -> sort transformation->ole db destination i came across this concept :If you have an ORDER BY clause in your TSQL statement in the OLE DB Source or the ADO.NET Source,you can notify SSIS that the data is already sorted, alleviating the need for the Sort Transform in the Advanced Editor. After ordering the data in your SQL statement, right - click the source and select Advanced Editor. Go to the Input and Output Properties and select the OLE DB Source Output. In the Properties pane, change the IsSorted property to True.If you have multiple columns, you could change this SortKeyPosition value to the column position in the ORDER BY statement starting at 1. A value of - 1 would sort the data in descending order. when i applied this am not getting the correct o/p.

If you already have a sorted resultset you don't need any Sort Component. The Sort in SSIS is very slow so it's recommended to sort in the source if ever it's possible. The Concept you wrote of is for Join components etc.

You have to add a ORDER BY in the source query... then go to the advanced editor and select True on the IsSorted property of the output columns: After that change the SortKeyPosition of all output columns that are sorted. Note 1 you have to set IsSorted=TRUE on the source adapter output, but setting this value does not perform a sort operation; it only indicates that the data it sorted. Note 2 The SSIS sort transformation sorts differently then SQL Server. For example SSIS sorts case sensitive and SQL Server does not. See this for more details. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Hi SSISJoost, This is the data stored in the source table “orders”. And the sql cmd specified in the OLE DB source editor is (select orderid,firstname,lastname,price from orders order by price,firstname. ) The ouput of the OLE DB source is Orderid firstname lastname price 2 Rita Shah 50 1 Joe Sam 100 4 Manjari Metha 150 6 Reema Sen 150 5 Shani Narsa 150 3 Shipra Soni 200 This output is given as an input to the sort transformation and I have specified the columns to sort in the sort transformation editor. In this case I have selected price and the sort type as descending. The output from this sort transformation is fed into an OLE DB destination table named “sortedorders” As I have specified a sql stmt which has an orderby clause in the OLE DB sourceI made the following changes to the OLE DB Source: 1. I went to advanced editor->input and ouput properties->OLE DB Source output->changed the Issorted propery to True. 2. I went to advanced editor->input and ouput properties->OLE DB Source output->output columns-> i selected price column and changed the sortkeyposition value to 1 and for firstname i changed the sortkeyposition value to 2. My question is how does the SSIS process this task? Will it consider the sort transformation ? Because the ouput which I get is different : Orderid firstname lastname price 3 Shipra Soni 200 4 Manjari Metha 150 6 Reema Sen 150 5 Shani Narsa 150 1 Joe Sam 100 2 Rita Shah 50

Do not use Sort Transformation for this case, you can set Sort properties in the oledb source, First right click on the oledb source, and select advanced editor, in the advanced editor window go to input and output tab, and select output, and change the IsSorted Property to true. Also you should expand the output, and select each column there and set SortKeyPosition based on orderBy clause, for example the SortkeyPosition for price should be 1 , and for Firstname Should be 2 . That's it , now your source is sorted. http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Thanks Reza Raad

You can set the sort information metadata through the advanced editor, but it does not affect the order of the rows returned by the provider. That is determined by your query. How are you checking the output from the OLE DB Source? Are you using a Data Viewer? Note that you can reorder the way the data is displayed in the data viewer by clicking on the column names (but that does not affect the data in the actual data flow buffer). Did you change the query between examples? Your first example appears correct. The second looks like what you'd get if your query had "ORDER BY price DESC, firstname"

Hi Matt MAsson, I used the data viewer to see the ouput of OLE DB source and also am i aware that we can reorder the data that is displayed in the data viewer. No, I didnt do any modifications in the query.