Transactions handling in SSIS package task

In SSIS a transaction can be configured at any level of container (package, container or task). By default transaction is not implemented at any level even not at task level. Let us test it:

Using SSMS create a table using below script:

CREATE TABLE tblTran(col1 int, col2 int)

In a SSIS package add an “Execute SQL Task” to insert records in tblTran table using below script:

INSERT INTO tblTran VALUES (10,20)

INSERT INTO tblTran VALUES (10,20aa)  --generate error by assigning a invalid value

 

Execute the task. Task will fail. Now check the records in table tblTran. We find that table has one record (values 10, 20). It means transaction is not supported at task level.

Now how can we implement transaction in SSIS?

SSIS support transaction at all levels of container. For transaction handling each container has TransactionOption property. By default its value is Supported. Integration Services provides three options for configuring transactions: NotSupported, Supported, and Required.

·         Required: indicates that the container starts a transaction, unless one is already started by its parent container. If a transaction already exists, the container joins the transaction. For example, if a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence container would start its own transaction. If the package were configured to use the Required option, the Sequence container would join the package transaction.

·         Supported: indicates that the container does not start a transaction, but joins any transaction started by its parent container. For example, if a package with four Execute SQL tasks starts a transaction and all four tasks use the Supported option, the database updates performed by the Execute SQL tasks are rolled back if any task fails. If the package does not start a transaction, the four Execute SQL tasks are not bound by a transaction, and no database updates except the ones performed by the failed task are rolled back.

·         NotSupported: indicates that the container does not start a transaction or join an existing transaction. A transaction started by a parent container does not affect child containers that have been configured to not support transactions. For example, if a package is configured to start a transaction and a For Loop container in the package uses the NotSupported option, none of the tasks in the For Loop can roll back if they fail.

 

Now its clear that a transaction starts if the TransactionOption property is Required. Let us test it by setting this property in “Execute SQL Task” that we created above. After setting this property to Required we find that either both statements execute successfully or no one.