SSIS data stream changes the order of records
I thought I asked a similar question in the SQL ordering and the outer join on the left is not in the correct order , but this is slightly different. I am fetching data from another SQL Server 2005 database using SSIS and standard data stream. The records I get are in a different order than in the original table (which is actually a view). Old DTS does not change the order.
There is no order in the source or target tables or views, and generally I would understand that if the order is important then it will be specified. I may need someone to specify the order and we can continue with that, but we are working with historical shape files, and if the order of the attributes changes, then the shapes are pointing to the wrong data.
Is there a reason why the order of data changes on import? Is there a job?
This is why order is important. This is a mapping application and the attributes and shapes must match. If the order changes, the items will not fit properly. Unfortunately I can't figure out any ordering of the data, and I suspect that the previous coder adopted a consistent order and that was all that mattered. The order using SSIS is not the same order and is never explicitly set.
a source to share
The order of the records returned by a SQL statement SELECT
is never guaranteed without an explicit clause ORDER BY
. You're just lucky that the dataset selected by DTS returned the rows in the order you want.
This is twice as true if the source is a view, since, as you say, views have no implicit order. Even adding TOP 100 PERCENT ... ORDER BY
does not guarantee the order in which the result set is returned (see the note at the top of the book on the Internet entry).
The only fix is to add a sentence ORDER BY
to your query in the data stream.
It's not clear from your question whether you have a group of columns in the view that is guaranteed to return you order - if so, update the question with more details on the original data, as there are several ways to approach the problem.
a source to share