CODEWITHSUNDEEP
t-sqlssistransactions
Badger8808
Badger8808

Reputation: 152

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

Upvotes: 0

Views: 652

Answers (2)

Badger8808
Badger8808

Reputation: 152

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

Upvotes: 0

Wouter
Wouter

Reputation: 2976

This is an option build into SSIS. You select the Sequence and go to properties. Under TransactionOption put the value to "required".
This requires MSDTC to be running, though.

Upvotes: 1

Related Questions