Retain The Order while Insert Rows

Question

I put a sort component to sort my data. and the data was sorted. but my destination table is unordered!

How can retain the Order of sorted rows while Inserting them into sql Table with ssis?

Answer 1

There is no inherent ordering of rows in a SQL Server table. You'll need to either add a 'sort order' column or write your queries so that they produce properly sorted result sets.

You can use an IDENTITY column as your 'sort order' columns, since it will increment as things get inserted.

Understand that repeated executions of a given query against a sql database are specifically not guaranteed to return results in the same order, so your queries need to do it each and every time.

Answer 2

Rows in a relational database do not have any "order" - they are like water molecules in a bucket! If you need to have an order then you must include another column that you can use to order by - e.g. an autoincrement field, a timestamp, or some column from external data. You can then use that column to order your data when you query it - otherwise you won't get ordered data.