A question about Control Flow SSIS

SSIS Control Flow Consider the following design: A Control Flow contains only a single Sequence Container. The Sequence Container contains three Execute SQL Tasks (Execute SQL Task 1, Execute SQL Task 2, and Execute SQL Task 3) connected by OnSuccess Precedence Constraints: Execute SQL Task 1 ==> Execute SQL Task 2 ==> Execute SQL Task 3. The Execute SQL Tasks update rows in three tables with each Execute SQL Task updating one and only one table. The Control Flow's TransactionOption property is set to Supported. The Sequence Container's TransactionOption property is set to Supported and the Sequence Container's IsolationLevel property is set too Serializable. The package is executed standalone (not from an Execute Package Task) with no overrides and Execute SQL Task 3 fails. What happens to the updates executed by Execute SQL Task 1 and Execute SQL Task 2? (Select the correct answer.) Choose your answer: th Execute SQL Task 1 and Execute SQL Task 2's updates are committed to the database. Execute SQL Task 1's update is rolled back. Execute SQL Task 2's update is rolled back. Both Execute SQL Task 1 and Execute SQL Task 2's updates are rolled back.

Correct answer:

th Execute SQL Task 1 and Execute SQL Task 2's updates are committed to the database.

Explanation:

The answer: A. Supported is the default TransactionOption property value. Since the package (Control Flow) TransactionOption is set to the default, the package is executed standalone, and there are no overrides applied at runtime; the execute SQL Tasks are not participating in a transaction. Serializable is the default IsolationLevel as well. Since the TransactionOption is Supported, Serializable is ignored until a transaction starts. To learn more about SSIS Containers and Transactions, attend Andy Leonard's Zero to SSIS class in the Boston area, October 6-8, 2010 (http://bostonsqltraining.com). You can read more about the class here.