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
1st Step Transaction Option: Required. TRUNCATION must occur before INSERTION.
“Every night the staging data is truncated and then all the recent orders from the online store database are inserted into the staging table”
2nd Step: Constraint: SUCCESS; Transaction Option: Supported
“If the Data Flow task that moves the data to the staging table fails, the entire refresh operation must be rolled back”
3rd Step: Constraint: COMPLETION; Transaction Option: Required
“For auditing purposes, a log entry MUST be entered in a SQL log table after each
execution of the Data Flow task”
So, the five boxes would come in the following order:
Required => Success => Supported => Completion => Required
Its a single transaction for the package (https://msdn.microsoft.com/en-sg/library/ms141144.aspx); the package initiates the transaction, so first box should be Supported.
Not sure abt the last box, its unclear if the audit log entry should be part of the transaction.
I think the last box should be NotSupported, like the answer states, because the entire operation will be rolled back in case the Log Execution fails. I don’t think that’s needed for only auditing functionality.
Agreed with Henk, if Supported, there’ll be no log as roll back on added records.
Supported => Success => Supported => Completion => NotSupported.