Don't expect your queries to be in order without a select statement, guess what same goes for SSIS!

Today I had to trouble-shoot a package (not developed by me or our SQL DBA Group), but regardless, it becomes our problem !

SSIS was exporting a table to a flat file, turns out this flat file is really an XML File.  The table is populated with XML from a stored procedure prior to the export.  The data pump ole db source was a table, turns out it's not always in order, resulting in malformed XML.  Change the source from table to a select statement with an order by clause and all is working well, fortunately there was a rowid (identity) column in the table already.

Frustruating to the end user, as it was a package that had been working fine for months.

Of course there was no primary key, making this a heap table; but either way, if the requirement is to have the data ordered, than you need an order by statement, even if the data is "in the table that way", and even for SSIS.  [Be nice if Microsoft put a warning on there that the data access mode may not be in order.]

This seems to be more of a problem on heap tables (no primay key) and Enterprise edition sql (which supports Merry-go-round scans [i think that's what it's called]).