DRAG DROP
You are designing a SQL Server Integration Services (SSIS) package. The package moves
order-related data to a staging table named Order. Every night the staging data is truncated
and then all the recent orders from the online store database are inserted into the staging
table.
Your package must meet the following requirements:
• If the truncate operation fails, the package execution must stop and report an error.
• If the Data Flow task that moves the data to the staging table fails, the entire refresh
operation must be rolled back.
• For auditing purposes, a log entry must be entered in a SQL log table after each
execution of the Data Flow task.
The TransactionOption property for the package is set to Required.
You need to design the package to meet the requirements.
How should you design the control flow for the package? (To answer, drag the appropriate
setting from the list of settings to the correct location or locations in the answer area.)
Answer: 
Explanation:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
http://msdn.microsoft.com/en-us/library/ms141144.aspx
TransactionOption: Required
Precedence Constraint: Success
TransactionOption: Supported
Precedence Constraint: Completion
TransactionOption: Required
0
0
Correct answer:
TransactionOption: Required
Precedence Constraint: Success
TransactionOption: Supported
Precedence Constraint: Completion
TransactionOption: Not Supported
You cant’t repeat the answer.
And if the Log fails, the prior tasks do not be rolled back.
0
0
While I believe Carlos has given the correct answer, you can indeed repeat the answer i.e. you can use any of 6 answer options more than once.
0
0
TransactionOption: Supported
Precedence Constraint: Success
TransactionOption: Supported
Precedence Constraint: Completion
TransactionOption: Not Supported
You _can_ repeat the answer.
Using Required is not necessary since at package-level it is already set to required.
-The TransactionOption property for the package is set to Required.-
It is explicitly stated that if the _Dataflow_ fails, the refresh should be rolled back. It does not say that a rollback should occur if logging is not successful.
– If the Data Flow task that moves the data to the staging table fails, the entire refresh
operation must be rolled back.-
0
0
So the first should be Required or Supported?
0
0
“The TransactionOption property for the package is set to Required”
Hence, So the first should be Supported?
1
0
Hence, the first should be Supported
0
0
as dbo said it should be Required.. why because they say ” a log entry must be entered in a SQL log table”
so if it fails it needs to stop.
Required.
0
0
They say ” a log entry must be entered in a SQL log table” for the auditing piece which should be set to not support any existing transactions. It needs to occur independent of the transaction i.e. “Not supported”.
I am open to be corrected of course, I’m just basing my answer on the training kit 🙂
1
0
==> 1Hint :: “entire refresh operation must be rolled back” means that
both TRUNCATE SQL TASK + DataFlow must be Rolled Back if somehow
the DataFlow fails. Consequently the only way to achieve that is
either
to put the TRUNCATE SQL TASK + DataFlow component in REQUIRED or
SUPPORTED TransactionProperty.
==> 2nd Hint :: The package is in REQUIRED transaction mode which means
that the package already started its own transaction. So the
Dataflow and TRUNCATE SQL Task don’t need to start their own
transaction, they only need to join the transaction started by the
package. So TRUNCATE + Dataflow should be in SUPPORTED Mode
==> 3rd Hint :: there is no specific information about managing the Logging SQL Task in case of fails which means that the logging task should be in NotSupported Mode.
Conclusion : The correct answer is :
TransactionOption: Supported
Precedence Constraint: Success
TransactionOption: Supported
Precedence Constraint: Completion
TransactionOption: NotSupported
2
0