SSIS Packages Containers Checkpoints and Transactions.
Hi evryone, I have a SQL server 2008 R2 on a windows 2008 server.
I have made all the configuration described in the article:http://blogs.technet.com/b/meacoex/archive/2011/03/20/bringing-the-transaction-mode-with-ssis-into-reality.aspx
but the package doesn't work as expected.
My example has also transactions in it: I have three sequence containers in cascade on success.
Each sequence container implements a transaction with two task:
1) Delete table A in a sql DB
2) copy data from a tab separated text file into table A.
If I change a value in a records in the text file in order to cause a PRIMARY KEY CONSTRAINT VIOLATION ERROR the package fails and a Checkpoint XML file is created as expected.
Then I change back the PRIMARY KEY to its original VALUE in the input text file (or I take the original file saved before modifying it) and I re-run the package. The package fails again with the same PRIMARY KEY CONSTRAINT VIOLATION ERROR as if the input text file were not changed.
If I delete the checkpoint file the package runs correctly thus confirming that the input file doesn't have any more duplicated keys in it.
Did anyone find this behaviour?
Is there any explaination for it?