If you're familiar with the way transactions are supported in DTS, it will help you to understand how transactions work in SSIS. In DTS, transaction properties were defined on the package and only one transaction was allowed per package. Steps/tasks were only in or out of a transaction. They didn't have any more options.
SSIS transactions are more flexible than that. It's possible to have more than one transaction per package and transactions scope is defined at the container level. Containers control transactions. This means that the container starts the transaction and either rolls it back or commits it. This can be a bit confusing if you don't understand that tasks are also considered containers. Actually, the taskhost is a container. It's a special container that only holds one object, the task. It's transparent in the Business Intelligence Design Studio (BIDS), but it's there and it's what controls transactions on a particular task.
Now, there are three “TransactionOptions” available per container.
- NotSupported - The container won't enter the transaction.
- Supported - The container will enlist in an existing transaction.
- Required - The container will create a transaction if none exists and enlist in an existing one.
So, this begs the question, “How does one know if a transaction 'exists'?” You look at the parents. If the parent container, or the parent of the parent or any ancestor up to the package “Requires” a transaction, a transaction exists.
As usual Jamie Thomson, from Conchango, has been pushing around the product and come up with some great questions. Thanks Jamie.
He has four SQL tasks in a workflow. One creates a table, the second inserts a valid value into it, the third inserts an invalid value into it and the third inserts a valid value. He asked why the third value goes in even though the second value was invalid. Why didn't the transaction roll the insertion back?
The answer is because, for his package, there are potentially 3 different transactions. One for each insertion. To make this scenario work, in other words, to allow the failed insertion (operation) to fail the rest, you need to put all the insert SQL tasks in the same container. Drop a sequence on the designer, drop all the SQL Tasks you want in the same transaction into that sequence. Set the transaction options to required. Set the transaction option on the sequence to required and now, if any of the SQL Tasks fail, the entire transaction will fail and none of the values will remain in the table.
A few things you should remember.
- Make sure the DTC is started in the services control panel.
- Transaction scope is defined by the containing container.
- If a transaction is already started on a parent, the child won't start another. It will use the existing transaction.
- If there is no transaction already started in any ancestor, and the transaction option is set to required, the container will start a new one.
- If there is no transaction already started in any ancestor, and the transaction option is set to supported, no transaction will be started.
Hope this helps. Let me know if you have any questions.
Thanks,
K