[RESOLVED] SSIS SQLServer 2005 Fail over

[Bill Crawley]

Hi All I have a series of Data Flows within a Sequence container. Each Data Flow Updates a single table in a destination from it's source.

The Updates themselves work fine, however If say my third dataflow fails, I want all processing to rollback (i.e. all data inserted from the first 2 sequences rolled back).

On each data flow I have FailPackageOnFailure and FailParentOnFailure set True.

Although the Sequence stops if an error is hit, that's all it does it does not rollback. I cannot find a specific 'Transaction' event to place all of these in.

Can you Help

[Bill Crawley]

Ok Folks, I managed to get to the bottom of the problem. I needed to remove the SQLScripts that did the transaction. At Package level Set the Transaction Option to Required and the Connections RetainSameConnection to False.

This solved my problem. If you have a similar and it still fails check the the DTC is running on the machine that the package is runnign on otherwise it will fail.