DTS 2000 gave us the ability to run transactions over our packages that enabled us to fail or succeed whole groups of atomic units of work...much in the same way that you can do using a traditional RDBMS. Transactions can even be run over multiple packages, over multiple data destinations, enabling us to commit or rollback very large groups of operations. Mind you, only 1 transaction can be run per package.
SQL Server Integration Services includes similar functionality although it is now much easier to configure, largely due to the new architecture. It also enables you to have multiple transactions in a package. Each container has a property called TransactionOption which is used to configure that container's participation in a transaction. If a container starts a transaction all child containers of that transaction have the option to enlist in that transaction. TransactionOption has 3 possible settings:
- NotSupported - The container will not enlist in a transaction, even if one was started by a parent container.
- Supported - The container will enlist in a transaction if one was started by a parent container. If a parent container did not start a transaction the container will not be enlisted in any transaction.
- Required - The container will enlist in a transaction if one was started by a parent container. If a parent container did not start a tranasction the container will start one itself.
I envisage that transactions will be used heavily with checkpoints in order to control package execution. Checkpoints are SSIS's method of package recovery in the event of failure. Note that, by design, a checkpoint will not function if it is placed on a container that enlists in a transaction started from a parent container.
Note that SSIS's support for transactions relies on Microsoft Distributed Transaction Coordinator (MS DTC) which must be running on the same machine that is running the SSIS package(s).
This is a very potted summation of how transactions are configured in SSIS. Look out for a more detailed overview, including test cases, coming out soon on SQLServerCentral.com!
Kirk has a good discussion around transactions on his blog.
Comments/questions are welcomed!
- Jamie