Transaction Control transformations allow you to commit or roll back individual records based on certain conditions. By default, Integration Service commits the data based on the properties you define at the session task level. Using the Commit Interval property, Integration Service commits or rolls backs the data into the target. Suppose you define Commit Interval as 10,000, Integration Service will commit the data after every 10,000 records. When you use a Transaction Control transformation, you get the control at each record to commit or roll back.
When you use the Transaction Control transformation, you need to define the condition in the expression editor of the Transaction Control transformation. When you run the process, the data enters the Transaction Control transformation in a row-wise manner. The Transaction Control transformation evaluates each row, based on which it commits or rolls back the data.
A sample mapping using the Transaction Control transformation is shown in the following screenshot:
To use the Transaction Control transformation in the mapping, perform the following steps:
- Open the mapping in Mapping Designer and create the Transaction Control transformation.
- Drag the required columns from the Source Qualifier transformation to the Transaction Control transformation.
- Connect the appropriate ports from the Transaction Control transformation to the target.
- Double-click on the Transaction Control transformation and click on Properties. We need to define the condition in the Transaction Control transformation expression editor, as shown in the following screenshot:
- Finally, click on OK.
The mapping using the Transaction Control transformation is now complete.
The Transaction Control transformation supports the following built-in variables in the expression editor:
TC_COMMIT_BEFORE: Integration Service commits the current record, starts processing a new record, and then writes the current row to the target.TC_COMMIT_AFTER: Integration Service commits and writes the current record to the target and then starts processing the new record.TC_ROLLBACK_BEFORE: Integration Service rolls back the current record, starts processing the new record, and then writes the current row to the target.TC_ROLLBACK_AFTER: Integration Service writes the current record to the target, rolls back the current record, and then starts processing the new record.TC_CONTINUE_TRANSACTION: This is the default value for the Transaction Control transformation. Integration Service does not perform any transaction operations for the record.
With this, we have seen the details related to Transaction Control transformations. It is not recommended that you use Transaction Control transformation in the mapping, as it hampers performance by checking each record for a commit or rollback. So the best way is to use the commit interval property in the session task.