During a user-defined commit session, the Integration Service commits and rolls back transactions based on a row or set of rows that pass through a Transaction Control transformation. The Integration Service evaluates the transaction control expression for each row that enters the transformation. The return value of the transaction control expression defines the commit or rollback point.
You can also create a user-defined commit session when the mapping contains a Custom transformation configured to generate transactions. When you do this, the procedure associated with the Custom transformation defines the transaction boundaries.
When the Integration Service evaluates a commit row, it commits all rows in the transaction to the target or targets. When it evaluates a rollback row, it rolls back all rows in the transaction from the target or targets. The Integration Service writes a message to the session log at each commit and rollback point. The session details are cumulative. The following message is a sample commit message from the session log:
USER-DEFINED COMMIT POINT Wed Oct 15 08:15:29 2003
WRT_8036 Target: TCustOrders (Instance Name: [TCustOrders])
WRT_8038 Inserted rows - Requested: 1003 Applied: 1003 Rejected: 0 Affected: 1023
When the Integration Service writes all rows in a transaction to all targets, it issues commits sequentially for each target.
The Integration Service rolls back data based on the return value of the transaction control expression or error handling configuration. If the transaction control expression returns a rollback value, the Integration Service rolls back the transaction. If an error occurs, you can choose to roll back or commit at the next commit point.
If the transaction control expression evaluates to a value other than commit, rollback, or continue, the Integration Service fails the session.
When the session completes, the Integration Service may write data to the target that was not bound by commit rows. You can choose to commit at end of file or to roll back that open transaction.
If you use bulk loading with a user-defined commit session, the target may not recognize the transaction boundaries. If the target connection group does not support transactions, the Integration Service writes the following message to the session log:
WRT_8324 Warning: Target Connection Group’s connection doesn’t support transactions. Targets may not be loaded according to specified transaction boundaries rules.