The Transaction Control transformation is an active transformation that commits or rolls back sets of rows during a mapping run. Use the Transaction Control transformation to commit or roll back transactions from transactional targets such as relational, XML, Amazon Redshift, and REST V2 targets. You can also use the transformation in a mapping to write data to a different flat file each time that Data Integration starts a new transaction.
You might want to use a Transaction Control transformation when you process large amounts of data. You can use the Transaction Control transformation to commit the data at certain intervals to prevent data loss. For example, you run a mapping that processes thousands of records in a table that is sorted by order type. You might want to commit the data each time that the mapping processes a different order type.
In a Transaction Control transformation, a transaction is the row or set of rows bound by commit or roll back rows. A transaction can be based on a group of rows that are ordered on a common key, such as employee ID or order entry date. The number of rows in each transaction can vary.
You define a transaction by specifying the transaction control condition in the transformation. Based on whether the condition is met, you can choose to commit rows, roll back rows, or continue processing data without changing the transaction boundaries.
When you run the mapping task, Data Integration evaluates the transaction control condition for each row that enters the transformation. When it evaluates a commit row, it commits all rows in the transaction to the targets. When Data Integration evaluates a roll back row, it rolls back all rows in the transaction from the targets.
If the mapping has a flat file target that is created at run time, you can generate an output file each time Data Integration starts a new transaction. You can dynamically name each target flat file.
Example
You want to use transaction control to write order information based on the order type. You want to ensure that all orders of a specific type are written to a different target file. To accomplish this, you create the following mapping:
The mapping contains the following transformations:
Source transformation
Configure the connection to the source ORDERS table.
Sorter transformation
Configure the sort condition to sort the source data by ORDER_TYPE.
Transaction Control transformation
Create the following transaction control condition to commit data when the Integration Service encounters a new order entry date:
Property
Value
Transaction Control Condition
If Field Value Changes
Field
ORDER_TYPE (string)
Then
Commit Before
Else
Continue Transaction
Target transformation
Create a new file target at run time and specify a dynamic file name. Use the following expression for the target name to create a different target file for each order type:
