SQL bit by bit transaction processing in SSIS

After introducing the transaction concept in SQL Server, we can continue to introduce this article.

We can include the entire package in SSIS in a transaction. But what if a table needs to be locked during package execution? The built-in transaction processing of SSIS can solve this problem.

The transaction processing options can be set in the SSIS package, container such as Loop, Foreach Loop, Sequence) or a separate task. The transaction processing options have the following values:

The built-in Transaction processing must use the Distributed Transaction Coordinator (MSDTC) service, which must be enabled. MSDTC allows distributed transaction processing, such as processing SQL Server databases and Oracle databases simultaneously in a transaction. If this service is not enabled, the following error message is displayed.

 
 

  
  
Error: 0xC001401A at Transaction: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.".  
  
  
The DTC transaction failed to start. This could occur because the MSDTC Service is not running.  
 
 

Note that the transaction isolation level of elements in SSIS is Serializable, which affects the lock duration. The following example shows how to lock a table in a package.

1. Create a SequenceContainer named Test Initialization.

2. This SequenceContainer is mainly used to create a test environment, create a connected table TranQueue, TranQueueHistory, and add a record to the first table to simulate a transaction processing process. We only use this SequenceContainer to create a test environment. Therefore, set its TransactionOption to NotSupported and add three Execute SQL statements to this SequenceContainer. Their settings are as follows:

 
 

  
  
/* Name */
  
  
Create TranQueue Table
  
  
/* SQLstatement settings */
  
  
If not exists (SELECT * FROM sys. objects WHERE object_id =
  
  

  
  
OBJECT_ID (N 'dbo. TranQueue ') AND type in (N 'U '))
  
  
BEGIN
  
  
Execute ('create TABLE dbo. TranQueue (message nvarchar (256 ))')
  
  
END
  
  
/* Name */
  
  
Populate TranQueue
  
  
/* SQLstatement settings */
  
  
Insert into dbo. TranQueue VALUES ('test message' + CONVERT
  
  
 
  
  
(NVARCHAR (23), GETDATE (), 121 ))
  
  
/* Name */
  
  
Create TranQueueHistory table
  
  
/* SQLstatement settings */
  
  
If not exists (SELECT * FROM sys. objects WHERE object_id =
  
  
 
  
  
OBJECT_ID (N 'dbo. TranQueueHistory ') AND type in (N 'U '))
  
  
BEGIN
  
  
Execute ('create TABLE dbo. TranQueueHistory (message nvarchar (256 ))')
  
  
END
 
 

3. Create the second SequenceContainer, name it Process, and set the TransactionOption attribute to Supported to add transaction processing.

4. Add an Execute SQL statement to this SequenceContainer and name it ProcessTranQueue. Its SQLStatement is set to the following statement. This statement simulates transaction processing and deletes the first 10 data records in the TranQueue table. The OUTPUT statement inserts the deleted data into the TranQueueHistory table, stops processing the simulation, and updates historical records.

 
 

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

5. Add an Execute SQL statement named Placeholder for Breakpoint. This task does not perform any operations, just to set a breakpoint here and then stop here to let us have time to verify whether the table will be locked.
 
6. Right-click the Control Flow interface and add the variable v_SimulateFailure. Its type is Int32 and its value is 1.

7. Add an Execute SQL statement named Simulate Failure. It is used to simulate errors and set SQLStatement to select 1/0. When pacakage is executed here, it will cause errors and roll back.

8. right-click the line between Placeholder for Breakpoint and Simulate Failure, click Edit, set Evaluation operation to Expression and Constraint, and set Expression to @ [User: v_SimulateFailure] = 1, keep others by default. In this way, when the value of the custom variable is 1, the execution will continue.
 
9. Run package. The result is 1. The package is terminated at the breakpoint.

 

Figure 1

10. open SQL Server Management Studion, select the corresponding database, create a new Query, execute the following statement, NOLOCK option ignore lock, this statement Query gets a record Message2011-04-10 14:22:31. 043, but this record is not submitted

 
 

  
  
SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)  
 
 

11. Execute the following statement

 
 

  
  
SELECT * FROM dbo.TranQueue  
 
 

The statement will be blocked here, and the statement will remain in the execution status and will not end. Because we use tablockx in the Process TranQueue task, here we will wait for the task to roll back or submit. Or you can write it like this, and it will even block

 
 

  
  
DELETE TOP(10) dbo.TranQueue  
  
  
INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))  
 
 

12. Click "Continue" or "Debuge". The package execution fails. SELECT * FROM dbo. TranQueueHistory WITH (NOLOCK) is executed. If rollback is performed, no result is obtained. SELECT * FROM dbo. TranQueue, either of which has a record.

 
 

  
  
SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)  
 
 

NOLOCK prompts ignore lock, this statement query gets a record Message2011-04-10 14:22:31. 043, but this record is not submitted

13. Execute the following statement,

 
 

  
  
SELECT * FROM dbo.TranQueue  
 
 

The SQL statement will be blocked here, and the statement will be executed all the time. Because we use tablockx in the Process TranQueue task, here we will wait for the task to roll back or submit. Or you can write it like this.

 
 

  
  
DELETE TOP(10) dbo.TranQueue;  
  
  
INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))， 
 
 

It can still block

14. Click "Continue" or "Debuge". The package execution fails. SELECT * FROM dbo. TranQueueHistory WITH (NOLOCK) is executed. If rollback is performed, no result is obtained. Execute SELECT * FROM dbo. TranQueue, either of which has a record.

If the value of the variable User: v_SimulateFailure is set to 0, the Simulate Failure task is not executed, and the rollback is not performed. The records in TranQueue are written to TranQueueHistory. Here is an interesting statement:

 
 

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

If some of the two tables have the same structure, you can use DELETE SourceTable output delete to import data from one table to another. */DELETE. column1, DELETE. column2... INTO DestinationTable FROM SourceTable, so that the data in the first table is "cut" to the second table.