Transactions in SSIS

Transactions in SSIS uses the Microsoft Distributed Transaction Coordinator (MSDTC). The MSDTC service needs to be started on the computer for Transactions to work. To enable the Transactions with in a package you need to set the TRANSACTION PROPERTY on control. For that we need to do in the following way. Right click on control, we have properties. In that properties we have option i.e Transaction Option.

Transaction option can be three types.

• Supported'
• Not Supported
• Requird

Default is Supported. This means if a Transaction exists join it.

We can prevent the task from patispating in a Transaction by setting  Transaction to not Supported.

If you set Transaction option set to Required, a new Transaction will be created for each loop of the container.