CORRECT TEXT
You are designing a package control flow. The package moves sales order data from a SQL
Azure transactional database to an on-premise reporting database. The package will run
several times a day, while new sales orders are being added to the transactional database.
The current design of the package control flow is shown in the answer area. (Click the
Exhibit button.)
The Insert New Orders Data Flow task must meet the following requirements:
Usage of the tempdb database should not be impacted.
Concurrency should be maximized, while only reading committed transactions.
If the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements.
How should you configure the transaction properties? To answer, select the appropriate
setting or settings in the answer area.
Answer: See the explanation
Explanation:
Select “IsolationLeve as Choas”
readcommitted
required
0
0
What ‘chaos’?!
IsolationLevel: ReadCommitted (worst-case scenario, you must still use ‘Serializable’)
TransactionOption: Required
**Usage of TempDB should not be impacted, so we cannot use ‘Snapshot’ isolation.
**Concurrency should be maximized, so read-committed seems most appropriate.
0
0
ream committed will less impact tempdb than snapshot
https://technet.microsoft.com/en-us/library/ms188277(v=sql.105).aspx
0
0
IsolationLevel = ‘Chaos’ doesn’t exist in SQL SERVER 2012, but in SQL SERVER 2016 it does. And CHAOS Isolation Level is quite similar to read Uncommited, which make this answer eliminated from the begining.
So we 3 potential answers here : Serializable, ReadCommitted and Snapshot.
Serializable : doesn’t apply with the requirement : “**Concurrency should be maximized”
Snapshot : doesn’t apply with the requirement : “**Usage of TempDB should not be impacted”
So the ISOLATIONLEVEL IS READCOMMITTED.
The TransactionOption is either “Supported” or “Required”. Because there is no specific hint about the TransactionOption of the package Parent, so we have no guarante that we’ll have a transaction lauched by the packagee.
So the TransactionOption = Required, with the Data flow would starts its Own Transaction.
So CAO’s answer is the rght one :
readcommitted
required
1
0