Transformation Guide

Back Next

Example

You want to use transaction control to write order information based on the order entry date. You want to ensure that all orders entered on any given date are committed to the target in the same transaction. To accomplish this, you can create a mapping with the following transformations:

Sorter transformation.
Sort the source data by order entry date.

Expression transformation.
Use local variables to determine whether the date entered is a new date.

The following table describes the ports in the Expression transformation:

Port Name	Expression	Description
DATE_ENTERED	DATE_ENTERED	Input/Output port. Receives and passes the date entered.
NEW_DATE	IIF(DATE_ENTERED=PREVDATE, 0,1)	Variable port. Tests current value for DATE_ENTERED against the stored value for DATE_ENTERED in the variable port, PREV_DATE.
PREV_DATE	DATE_ENTERED	Variable port. Receives the value for DATE_ENTERED after the Integration Service evaluates the NEW_DATE port.
DATE_OUT	NEW_DATE	Output port. Passes the flag from NEW_DATE to the Transaction Control transformation.
The Integration Service evaluates ports by dependency. The order in which ports display in a transformation must match the order of evaluation: input ports, variable ports, output ports.

Transaction Control transformation.
Create the following transaction control expression to commit data when the Integration Service encounters a new order entry date:

IIF(NEW_DATE = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

The following figure shows a sample mapping using a Transaction Control transformation:

The mapping contains two sources joined to one source qualifier, a Sorter transformation, Expression transformation, Transaction Control transformation, and a target.

Rename Saved Search

Table of Contents

Transformation Guide

Transformation Guide

Example

Example