Defining loop variables

You can declare and use loop variables within a Transformer stage. You can use the loop variables in expressions within the stage.

You can use loop variables when a loop condition is defined for the Transformer stage. When a loop is defined, the Transformer stage can output multiple rows for every row input to the stage. Loop variables are evaluated every time that the loop is iterated, and so can change their value for every output row. Such variables are accessible only from the Transformer stage in which they are declared. You cannot use a loop variable in a stage variable derivation.

Loop variables can be used as follows:

  • They can be assigned values by expressions.
  • They can be used in expressions which define an output column derivation.
  • Expressions evaluating a variable can include other loop variables or stage variables or the variable being evaluated itself.

Any loop variables you declare are shown in a table in the right pane of the links area. The table looks like the output link table and the stage variables table. You can maximize or minimize the table by clicking the arrow in the table title bar.

The table lists the loop variables together with the expressions that are used to derive their values. Link lines join the loop variables with input columns used in the expressions. Links from the right side of the table link the variables to the output columns that use them, or to the stage variables that they use.

  1. Select Loop Variable Properties from the loop variable pop-up menu.
  2. In the grid on the Loop Variables tab, enter the variable name, initial value, SQL type, extended information (if variable contains Unicode data), precision, scale, and an optional description. Variable names must begin with an alphabetic character (a-z, A-Z) and can only contain alphanumeric characters (a-z, A-Z, 0-9).
  3. Click OK. The new loop variable appears in the loop variable table in the links pane.
    Note: You can also add a loop variable by selecting Insert New Loop Variable or Append New Loop Variable from the loop variable pop-up menu. A new variable is added to the loop variables table in the links pane. The first variable is given the default name LoopVar and default data type VarChar (255), subsequent loop variables are named LoopVar1, LoopVar2, and so on. You can edit the variables on the Loop Variables tab of the Stage Properties window.
    Figure 1. Example Transformer stage with loop variable defined
    Shows a Transformer stage with a loop variable defined