Hi,

We are trying to load several Excel files into SQL Server SSIS and we need to save the data in the database in the same order as it is in Excel..

My question is, Does SSIS guarantee that it loads the data into SQL Server in the same order as it is in Excel. If so, we will add a sequence to ensure we have the order.

Please advise.

Thanks & Regards,

Dhanumjay

Hi Dhanumjay, 

If there's nothing in between (Sort operators in SSIS, Union Alls, etc) I'd say yes, it will load it following the same order. However, I wouldn't trust an Excel data read, so the easiest way to ensure that you have the same order is either you put an index key in your Excel file or you build an index on your table following the order you want to force data to follow. Or both :)

Regards.

Pau

Thanks for your response.

If it is one file then we can add an index column, but we have to load hundreds of files.

How adding an index/key column to the table works unless SSIS picks and loads the data in the table in the same order as it is in Excel?

Just to explain my question better,

If excel has three rows  {a,b},{c,d},{e,f}, does SSIS ensure it loads the data in the same order in a table.

Thanks.

Hummm, this is a good question.  I'm not totally sure about the order that SSIS loads data into a DB table, but I guess it doesn't matter, right.  The data is definitely NOT ordered in the Table.  If you want the data to be ordered, use a Select ... Order By query.

SSIS will not re-order rows in your data flow unless you use an blocking operator in your data flow.  However SQL Server does not guarantee that the order will be preserved on bulk load. 

You should add a column to the data and assign the row numbers yourself. 

David