September 18, 2008 at 4:29 am
In a sequence container, I have a Populate County2 Dataflow Task, and a Delete Staging Execute SQL task. In the Dataflow task, a single table called 'County2' is populated from 'County2_Staging'. These tables are in a sql server 2005 database that runs on the 64 bit edition.
After the Populate County2 Dataflow task completes successfully, the records in the staging table are deleted from County2_Staging in the Delete Staging SQL task.
Before the Populate County2 Dataflow task, I have an Execute SQL task that starts a transaction:
BEGIN TRANSACTION
After the Populate County2 Dataflow task and the Delete Staging Execute SQL task, I have another Execute SQL Task that commits the transaction:
COMMIT TRANSACTION
If an error occurs within the sequence container, the sequence container is failed. I have an Execute SQL Task in the OnTaskFailed event of the sequence container. All of the containers in the sequence container have FailParentOnFailure set to True.
The problem I am having is that when the sequence container fails, the Delete Staging Execute SQL task is rolled back, but the insert that occurs between County2_Staging and County2 in the Populate County2 Dataflow task does not.
Why won't the insert in the Dataflow task roll back? The RetainSameConnection property of the connection manager (ole db for sql server) is set to True'.
Thank you for your help!
CSDunn
September 18, 2008 at 1:24 pm
It looks like using the same connection manager (RetainSameConnection = True) between the very first Execute SQL Task in the sequence container (the one that issues BEGIN TRANSACTION) and the destination in the Dataflow, while using a different connection manager in the Dataflow source (points to the same database) does the trick.
CSDunn
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply