Implement transactions in SSIS.

• You can set package transactions at the entire package level or at any control flow container level or task level.

• Transactions in SSIS use the Microsoft Distributed Transaction Coordinator(MSDTC); the MSDTC service needs to be started on the computer for transactions to work. 

• To enable a transaction within a package, you need to set the TransactionOption property of the task or container to Required. The TransactionOption property exists at the package level and at the container level, as well as for almost any control flow task. 

It can be set to one of the following:

• Required : If a transaction already exists, join it; if not, start a new transaction.
• Supported : If a transaction exists, join it (this is the default setting).
• NotSupported : The package, container, or task should not join an existing transaction.

If a series of tasks must be completed as a single unit in which either all the tasks are successful and committed or an error occurs and none of the tasks are committed,place the tasks within a sequence container and set the TransactionOption property of the container to Required.

Create the simple package.

This package is quite basic in that all it does is insert some data into the table
and then the last task will deliberately fail. Open SSMS and run the following code.

CREATE TABLE Transaction_test
(
id int
)

In the first task, insert some data into the table you just created with the following code:

Insert into Transaction_test(id) values (1)

To make the final task fail at runtime, enter the following code:

Insert into Transaction_test(id) values ("A")

Run the package with no transactions in place and see what happens.

The first task succeeds, and the second fails.If you go to your database, you should see data inserted.

Now run the following code in SSMS to delete the data from the table:

Truncate table Transaction_test

Next, you want to set up the package to start a transaction that the tasks can join.You do that by
setting the properties of the package.Set the TransactionOption property to Required.

You now need to tell the tasks in the package to join this transaction, by setting their
TransactionOption properties to Supported.

Now when you re-execute the package, a DTC transaction will be started by the package, all the
tasks will join, and because of the failure in the last task, the work in the package will be undone.
Go back to SSMS and query the table. You should see no data in it.