The Transaction Control transformation is Active and Connected which lets control the commit and rollback transactions based on a set of passing-through data. A commit or rollback can be defined based on a varying number of input rows, for example a common product group key (to ensure that the entire group gets loaded) or invoice date (to avoid loaded an incomplete set of invoices for a given date into a data warehouse).
A transaction is the row or set of rows bound by commit or roll back rows. The number of rows may vary for each transaction.
Transaction control can be defined at two levels:
- Within a mapping - a transaction is defined using an expression in a Transaction Control transformation. Based on the return value of the expression, the decision can be made whether to commit, roll back, or continue without any transaction changes.
- Within a session - when a session is configured for user-defined commit, the adminisitrator can choose to commit or roll back a transaction if the Integration Service fails to transform or write any row to the target.
The Transaction Control transformation can be used to define conditions to commit and roll back transactions from transactional targets, such as: relational databases, XML, and dynamic MQSeries targets.
The following built-in variables can be used in the Expression Editor to create a transaction control expression:
Examples / useful tips
To catch date change in the invoices flow, the NEW_DATE indicator is calculated within an Expression Transformation with this expression: IIF(INV_DATE_CURRENT=INV_DATE_PREV, 0,1).
To commit all invoices for a given day the following transaction control expression might be used: IIF(NEW_DATE = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)