July 11, 2006 at 11:21 am
I have developed a package creates a result set of table names. I use this result set in a ForEach container. Inside of this container I INSERT rows into one table via an ExecuteSQL task and then DELETE rows from another table on a different server via another ExecuteSQL task. By setting the ForEach container to a TransactionOption of "Required" I have been able to put these into a transaction. The problem is that the transaction endures throughout each and every loop. I would like to COMMIT the changes at the end of each loop so that I don't have a huge transaction running. Is there a way to do this in SSIS? In DTS you could set the workflow properties to commit after the step completed. I can't seem to replicate this behavior in SSIS.
Thanks,
George
July 11, 2006 at 1:52 pm
I think I've figured this out. I put the steps required in the transaction inside of a Sequence Container which is inside of the ForEach Loop container. I removed the "Required" transaction setting from the ForEach container and changed the Sequence container to have a TransactionOption of "Required". Each time the Sequence container is entered into on the loop a distributed transaction is started and each time it leaves the Sequence container successfully the transaction is committed. This seems to have the desired effect.
Thanks,
George
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply