You are creating a SQL Server 2008 Integration Services (SSIS) instance for Company.com.
The package displays job listed below:
– You should operate multiple files through utilizing a For Each Loop container.
– You should get the files’ content to a table through utilizing a Data Flow task.
– Through utilizing an Execute SQL task, you should Log the results of the state into a table .
– You should make sure that the Execute SQL task only could not run in one transaction.
Which is the correct answer? (Select more than one)
A.
You should set the TransactionOption property to RequiredNew in the package.
B.
You should set the TransactionOption property to Supported in the package.
C.
You should set the TransactionOption property to Required in the package.
D.
You should set the TransactionOption property to None in the package.
E.
You should set the TransactionOption property to NotSupported in the Execute SQL task.
F.
You should set the TransactionOption property to Supported in the Execute SQL task.
Explanation:
To enable a transaction within a package, you need to set the TransactionOption property of the task or container to Required. Figure 2-1 highlights the properties of a package at the control flow level, which means that the properties apply to the package as a whole. The TransactionOption property is the same on any control flow object.
When deciding whether and how to implement a transaction, follow these guidelines:
– For transactions to be enabled in SSIS, you need to start the MSDTC service, and the tasks that you want to be part of the transaction must work with the MSDTC service natively.
– If a series of tasks must be completed as a single unit in which either all the tasks are successful and committed or an error occurs and none of the tasks are committed, place the tasks within a Sequence Container and then set the TransactionOption property of the container to Required.
– A task can inherit the transaction setting of its parent when the TransactionOption property is set to Supported, which is the default setting when creating a task or container.
– You can prevent a task from participating in a transaction by setting its TransactionOption property to NotSupported.
– If you set the TransactionOption property of a Foreach Loop Container or For Loop Container to Required, a new transaction will be created for each loop of the container.
– Transactions work at the control fl ow level and not within a data flow. Therefore, you can turn on a transaction for a Data Flow Task, but you cannot turn it on separately for selected components within the data fl ow; either the entire data process will be successful or it will be rolled back.