The Data Integration Service identifies which transformations have side effects before it optimizes a mapping. The Data Integration Service assumes that a transformation has side effects when it cannot determine if the transformation has side effects.

Transformations with side effects limit when the Data Integration Service can optimize the mapping. Early selection, branch pruning, global predicate optimization, and push-into optimization alter mapping results if the Data Integration Service applies them to a transformation that has side effects. With early selection and push-into optimization, filter logic is moved from a Filter transformation as close to the source as possible. If the filter occurs before the side effect function, the mapping results change.

For example, a transformation receives a customer ID and returns rows containing order information. The transformation also writes the orders to a file. If the Data Integration Service applies a filter optimization before it writes orders to the file, the file receives less rows than when the filter occurs later in the mapping. The transformation side effect is the function of writing the order records to a file.

The following transformations have side effects:

SQL transformation, Web Service Consumer transformation, and Java transformation unless the side effects property is disabled.
Transformations that call an ABORT() or ERROR() function, send email, or call a stored procedure.
Transformations that write to files or databases.
Transformations that maintain a count through a variable port. For example, COUNT=COUNT+1.

The SQL transformation, Web Service Consumer transformation, and Java transformation have side effects by default. If you configure the transformation to process rows without side effects, you can disable the

Has Side Effects

property in

Advanced Properties

. If the transformation does not have side effects, you can enable optimization by configuring additional properties in these transformations.