Manage Transactions in SSIS
Transaction is used to maintain data integrity. We have options to create a transaction and manage in Database management system. In the same manner , we can manage transactions in SSIS by using TransactionOption property.
This property is available in packages, For Loop, Foreach Loop, and Sequence containers, and the tasks. These components can be used as a single unit task for the transaction.
The meaning of transaction is about commit or rollback the data in the transaction of SSIS package.
Distributed transactions carry this concept a step further. So, we should start DTC (i.e. MSDTC) Service before package execution.
Required – Indicates that the container starts a transaction, unless one is already started by its parent container.
Supported – Indicates that the container does not start a transaction, but joins any transaction started by its parent container.
NotSupported – Indicates that the container does not start a transaction or join an existing transaction.
Summary: In this example, I will be using a table and two execute SQL task and a sequence container.
Create a table named Nation with a column nation_name varchar(10) and Add few rows in it.
Step 1: Create a package and add a sequence container and include two execute SQL tasks in it.
Step 3: Configure Execute SQL Task
Step 4: Configure Execute SQL task 1
Step 6: Now, check the Nation table. Even-though execute SQL Task executed successfully, it will be rollback due to the transaction failure. Because, Sequence container is a transaction and Execute SQL tasks are child transaction.
Step 7: Now, Set property ForceExecutionResult = None of Execute SQL Task 1 and execute package.
Thanks for reading.