SSIS Data Flow changes order of records

Question

I thought I asked a similar question in http://stackoverflow.com/questions/462071/sql-order-by-and-left-outer-join-doesnt-have-correct-order, but this is slightly different. I am getting data from another SQL-Server 2005 database, using SSIS, and a standard data flow. The records that I get are in a different order than in the source table (actually it's a view). In the older DTS, the order doesn't change.

There is no ordering on the source or destination tables or views, and normally, I'd figure that if the order was important, then it would be specified. I may have to get someone to specify an order, and we can go forward with that, but we're working with historical shape files, and if the order of the attributes change, then the shapes are pointing to the wrong data.

Is there a reason why the data changes order when it is imported? Is there a work-around?

Here is the reason the order is important. It is for a mapping application, and the attributes and the shapes have to match. If the order changes, then the items won't match up properly. Unfortunately, I can't identify any order to the data, and I suspect that the previous coder assumed a consistant order, and that was all that was important. The order, using SSIS, is not the same order, and it is never explicitly set.

Answer 1

The order of records returned by a SQL server SELECT statement is never guaranteed without an explicit ORDER BY clause. You were simply lucky that the data set which DTS selected returned the rows in the order you required.

This is doubly true if the source is a view since, as you say, views have no implicit order. Even adding TOP 100 PERCENT ... ORDER BY doesn't guarantee the order in which the result set is actually returned (see the note at the top of the books online entry).

The only fix is to add an ORDER BY clause to your query in the data flow.

It's not entirely clear from your question whether you have a group of column(s) in the view which is guaranteed to return the order you need - if so update the question with some more details about the source data, as there are several ways to approach the problem.