transaction in ssis is not working

The Test Initialization sequence container is used to create a test environment. Two tables are created (TranQueue and TranQueueHistory) and a row is inserted into TranQueue. This will allow us to simulate a process where the SSIS package processes a group of rows inside of a transaction. The TransactionOption setting for the Test Initialization sequence container is NotSupported since it only exists to create the test environment; i.e. we don't need any transaction support here which would rollback any successful steps in the event of a failure.

The Process sequence container has its TransactionOption set to Supported; since the package setting for TransactionOption is set to Required, a transaction is created at the package level and the container will join that transaction. Process TranQueue is an Execute SQL task that executes the following SQL command to simulate processing a group of rows in the TranQueue table:

DELETE TOP(10) dbo.TranQueue OUTPUT DELETED.* INTO dbo.TranQueueHistory FROM dbo.TranQueue WITH (TABLOCKX)

The main points about this SQL command are:

• It deletes the first ten rows from the TranQueue table to simulate pulling them out for processing
• It uses the OUTPUT clause to insert the message column of each deleted row into the TranQueueHistory table to simulate processing has completed and history is being updated
• It uses the TABLOCKX table hint to lock the TranQueue table

The Placeholder for Breakpoint Execute SQL task does not execute a command; it's there so we can set a breakpoint and run some queries while the package is running and the transaction is open (discussed below). The Simulate Failure Execute SQL task is executed if the package variable v_SimulateFailure = 1; it does a SELECT 1/0 to generate an error (i.e. a divide by zero) which will cause a rollback on the package transaction.

The above example is intentionally short just for demonstration purposes. You can certainly have multiple tasks in the Process sequence container, all of which would participate in the transaction, and either all succeed on none succeed (i.e. rollback on failure).

You can download the project containing the sample SSIS package here. The package is hard-coded to use a local database named mssqltips; create it if it doesn't exist. Open the project using SQL Server Business Intelligence Development Studio (BIDS) and double click on the package Transaction.dtsx. Follow these steps to see the transaction handling in an SSIS package:

• Make sure the value of the variable v_SimulateFailure = 1; this will demonstrate the rollback
• Make sure there is a breakpoint on the Placeholder for Breakpoint Execute SQL task
• Execute the package; your screen should look like this (stopping at the breakpoint):

• Open a new query window in SQL Server Management Studio, connect to the mssqltips database and execute the command below. You should see a single row result set; e.g. Test Message2008-09-08 14:22:31.043 (your date and time will be different of course). The NOLOCK hint ignores locks; the row you see is not committed yet.

• Open another new query window in SQL Server Management Studio, connect to the mssqltips database and execute the command below. You will be blocked waiting for the transaction executing in the SSIS package to either rollback or commit since we added the TABLOCKX hint which will keep the TranQueue table locked for the duration of the transaction. Alternatively you could issue an INSERT INTO the dbo.TranQueue table and you will see that it also is blocked until the transaction either commits or does a rollback.