Sorting Data in SSIS

Question

Post reply

Sorting Data in SSIS

Shaira

SSCrazy

Points: 2432
More actions
July 9, 2010 at 12:19 pm

#221074

I am having troubles sorting data in my table using SSIS.
I have a package that loads and manipulates my data in a table - all is good. But my last step, I want to sort the data in this table. I tried adding ORDER BY in an Execute SQL task to the package, it runs with no errors but the data in the table never gets sorted. I've also tried adding a Data Flow task and adding the Sort task but again, the data is never put in the order I want (one column ascending order). This seems like such a simple chore and I know I'm just not seeing the obvious. Can someone help? Thanks in advance!!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

Is the step that initially loads the data, the step where you added the ORDER BY?

Shaira SSCrazy Points: 2432 More actions · Answer 2

No - in a data flow task I load the data from a flat file, trim and change some string dates to actual dates, and then load that data into my table. Once the data is in the table, in the Control Flow, I use an Execute SQL task to create a 'key' field (for lack of a better word) based off several fields in the table. This 'key' field is what I want to sort by. I have tried to added another Execute SQL task ... another Data Flow task with the Sort task in it ... but none sort the data in my table. If I run a select */order by statement in SSMS, the data sorts as expected.

Suggestions???

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 3

Are you trying to physically resort the actual table, or just the data in the data stream in your data flow?

If you are trying to physically resort table, I would put an index constraint on the table before you load it, in either case this may be your answer.

If you are just concerned with the data stream, start a new data flow task after the table is initially loaded, and query the table with an order by clause as the first step in your data flow and then proceed with the rest of your tasks.

I generally try to avoid the SSIS sort unless there is no other way. It takes up a lot of memory and really slows down the overall package.

Hope this helps.

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 4

Did you set all the options correctly?

here is the link:

http://msdn.microsoft.com/en-us/library/ms187653.aspx

Cheers,
Hari
Tips & Tricks for SQL BI Developers

anilpinnamaneni SSC-Addicted Points: 493 More actions · Answer 5

Hi,

I have faced similar problem before and finally we have eneded up in creating a index to store the data seqentially.

Neither the order by or sort transformation will help in inserting the data sequentially in to the table

Regards

Anil

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

anilpinnamaneni (7/14/2010)
Neither the order by or sort transformation will help in inserting the data sequentially in to the table

That is because that is not the purpose of those methods.

The ORDER BY clause orders the results of a query but it doesn't touch the physical structure of a table.

The sort transformation will sort the data in memory in SSIS (but it blocks the pipeline, so it is to be avoided) but again, it has nothing to do with the physical layout.

An option to get your sorted data is to create a view on top of the table (or create an index, but these should be build for performance reasons, not just to sort the data).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Rich Mechaber SSChampion Points: 11130 More actions · Answer 7

Perhaps I'm missing something, but if you truly mean "table" when you say table - that is, a table in the database, outside of SSIS - then you cannot and should not try to sort it. Why would you want to?

Or are you trying to do the sort in SSIS? As someone else pointed out, SSIS sort tasks are notoriously slow. You can get around it often enough by using an ORDER BY clause in whatever T-SQL you use to generate the data set you're manipulating.

Rich

steve block SSCertifiable Points: 7698 More actions · Answer 8

As others have alluded to, there is no inherent order to data in a relatational database thus trying to sort it before inserting it has no value. To retrieve data in a particular order, use an ORDER BY in your select statement. To improve performance on the select, add an index on appropriate fields. It is also best practice to add an 'actual' Primary Key to the table.

In summary, don't sort with SSIS but use the database tools to get the proper order.

Steve