SSIS Package and XML

SSIS and Import/Export
1

Hi.

We have 3 environments which are identical in nature and there are SSIS packages on them that create XML files which have a sequential record structure in them i.e. Rec 1, Rec 2, Rec 3 etc. Everything works as expected.

Now we have recently introduced a 4th environment whereby we are using SQL clustering. We installed the SSIS Packages (had to change the config file to add a servername of the other SQL Server) and the package runs ok.

However we find that all XMLs produced have records that are out of sequence i.e. Rec1, Rec3, Rec5, Rec2, Rec4, Rec6, We migrated everything like for like. Is there something in SSIS that when run in a clustered environment would cause this behaviour?

At a Complete loss with this one.

Thanks
Kam

2

Nothing in a clustered environment is going to cause query results to be ordered differently. What will cause query results to be ordered differently is not putting an ORDER BY clause on the select statement outputting the data to the client.

If you are assuming that inserting data into a table variable in a particular order will insure that order is retained when selecting from the table variable - that is not true. We ran into an issue when we upgraded from 2008 R2 to 2012 where the developer was inserting in a table variable in a specific order...it worked on 2008 R2 most of the time but did not work in 2012 anymore.

1 Like
3

Thanks Jeff.

I don't disagree with what you are saying. I just cant get my head round why the package works in 3 environments apart from the clustered one. Bit lost here now will keep looking but thanks for getting back to me

4

First - what is the code being used to extract the data into SSIS?
Second - what objects in SSIS are you using and what are the precedence constraints?

If you are getting mixed results then either the data is being extracted in that order - or you have multi-threaded the SSIS processing and that process is allowing it to insert into the final location with a different order.

A simple fix would be to use a sort operator in SSIS to insure the records are sorted appropriately before outputting the records to the final destination.