Maintaining Data Consistency with Transactions

Transactions are used to maintain data integrity by ensuring that a database remains in a consistent state even if a package fails. They accomplish this by binding the database actions that tasks perform into units, which either succeed or fail together. Because all the database actions that are part of a transaction are either committed or rolled back together, you can ensure that data remains in a consistent state. For example, if you have a package with multiple data flow tasks, each updating and inserting data into a different database table, and the package fails, all of the database update actions performed by the data flow tasks are rolled back together, thereby ensuring a consistent state. If all the data flow tasks succeed, the changes performed are committed together.

Configuring Transactions

Transactions can be enabled for all SSIS container types, including tasks, containers, and packages. You configure transactions by using the container's TransactionOption property, which is set in the SSIS design environment. The TransactionOption property supports the following values:

• Not Supported The container does not start a transaction and will not join an existing transaction even if it has already started.

• Supported The container does not start a transaction but will join an existing transaction that has already started.

• Required The container starts a new transaction. If an existing transaction has already been started by the parent container, the container will join it.