You design a Business Intelligence (BI) solution by using SQL Server 2008. You have a SQL Server 2008 Integration Services (SSIS) package that runs against a SQL Server 2008 data source. The package contains an opening Execute SQL task that runs the BEGIN TRANSACTION command. This is followed by a Sequence task that contains additional Execute SQL tasks, each with the FailParentOnFailure property set to TRUE .
There are two Execute SQL tasks. The first task is connected to a Success precedence constraint that runs the COMMIT TRANSACTION command. The next task is connected to a Failure precedence constraint that runs the ROLLBACK TRANSACTION command. The package fails but the transaction is not rolled back. You need to ensure that the transaction is successfully rolled back if the package fails.
What should you do?
A.
Modify the RetainSameConnection property as True for the Connection Object.
B.
Modify the TransactionOption property as Required for the Sequence Container.
C.
Modify the TransactionOption property as Required for each Execute SQL task.
D.
Modify the IsolationLevel property as ReadCommitted for each Execute SQL task.
Explanation:
Tip: "COMMIT TRANSACTION … ROLLBACK TRANSACTION" = "RetainSameConnection"You can use Execute SQL tasks to issue your own
BEGIN TRANSACTION and COMMIT TRANSACTION
commands to manually start and stop the transactions. Be aware that, if you take this approach, you must set the RetainSameConnection property of the connection manager to True.
This is because the default behavior in SSIS is to drop the connection after each task completes, so that connection pooling can be used.
(Smart Business Intelligence Solutions with Microsoft SQL Server 2008, Copyright 2009 by Kevin Goff and Lynn Langit)