Search code examples
t-sqlssistransactions

Is it possible to wrap a container in SSIS with a transaction using 2 Execute SQL tasks?

I want to wrap the sequence container in a transaction, so that the user can choose whether to insert their data.
However, with

BEGIN TRANSACTION T1;

in "Begin Transaction" and

IF ? = 0
   COMMIT TRANSACTION T1
ELSE
   ROLLBACK TRANSACTION T1;

in "Rollback or Commit Transaction", an error is thrown saying that the last task does not have a begin statement that it binds too.

Control Flow of how I want the transaction to work

Is there another way to achieve a transaction over a container, or do some package properties need to be changed for this to work?

P.S. The package will be ran by a C# form, so if there is a way to instead wrap the package in a transaction from C#, that option is also available

Thanks for any help or advice

Solution

  • By setting all tasks to Supported on TransactionOption, and adding checks to see if there is a transaction active, like this

    DECLARE @TranCount int;

SET @TranCount = (select COUNT(*) from sys.sysprocesses WHERE open_tran > 0);

IF @TranCount > 0
ROLLBACK TRANSACTION;

    The whole container is wrapped in a transaction, and rolls back / commits without error

    enter image description here