Microsoft
70-463 · Question #115
70-463 Question #115: Real Exam Question with Answer & Explanation
The correct answer is D: Required.. Setting the SSIS package TransactionOption property to 'Required' forces all Execute SQL tasks to participate in a single distributed transaction that rolls back entirely if any task fails.
Question
You have been asked to revise a SQL Server Integration Services (SSIS) package. The package only includes Execute SQL tasks. To make sure that, in the event of an Execute SQL task failing, changes made by all of the Execute SQL tasks is rolled back. To do this, you want to alter the TransactionOption property of the package. Which of the following is the value that must be set for the TransactionOption property of the package?
Options
- ANotSupported.
- BSupported.
- CNotRequired.
- DRequired.
Explanation
Setting the SSIS package TransactionOption property to 'Required' forces all Execute SQL tasks to participate in a single distributed transaction that rolls back entirely if any task fails.
Common mistakes.
- A. NotSupported means the package explicitly opts out of transactions entirely, so no rollback mechanism exists if a task fails.
- B. Supported means the package will join an existing ambient transaction if one is present, but will not start its own - leaving no rollback guarantee when the package runs standalone.
- C. NotRequired is not a valid value for the TransactionOption property; the three valid enumeration values are NotSupported, Supported, and Required.
Concept tested. SSIS package TransactionOption Required property for atomic rollback
Reference. https://learn.microsoft.com/en-us/sql/integration-services/integration-services-transactions
Community Discussion
No community discussion yet for this question.