Table Iterator

Table Iterator Component

Loop over rows of data within a table or view and run another component for each row.

This component implements a simple loop over rows of data in an existing table. It enables you to run an
attached component multiple times, each time with different values set on any number of variables taken from
columns of that table. Those variable can then be referenced from the attached component.

To attach the iterator to another component, use the blue output connector and link to the desired component. To detach, right click on the attached component and select 'Disconnect from Iterator'.

If you need to iterate more than one component, put them into a separate orchestration
or transformation job and use a Run Transformation or Run Orchestration component attached to
the iterator. In this way, you can run an entire ETL flow multiple times, once for each row of
variable values.

The iterations are set-up in advance so the connection to the input table can be closed before
any iterations are performed. If the attached component modifies the iteration table, those
changes will not be reflected during the current run. Furthermore, to control runaway processes and
control resources, only a limited number of rows of a table are considered for iteration.
If you are iterating many rows, it would almost certainly be better to use the table in a transformation and
join the table being iterated instead.

Properties

Property

Setting

Description

Database

Select

Select the database that the iterated tables belong to.

Schema

Select

Select the table schema. The special value, [Environment Default] will use the schema defined in the environment. For
more information on using multiple schemas, see
this article.

Name

Text

The descriptive name for the component.

Concurrency

Select

Sequential - Iterations are done in sequence, waiting for each to complete before starting the next.
This is the default.
Concurrent - Iterations are run concurrently. This requires all "Variables to Iterate" to be defined as
Copied variables, so that each iteration gets its own copy of the variable isolated from the same
variable being used by other concurrent executions.
Note: The maximum concurrency is 8 times the number of processors on your cloud instance. For example: An instance with 2 processors has a maximum concurrency of 16.

Table Name

Select

Choose an existing table or views to iterate.

Column Mapping

Column Name

Enter the table column name.

Variable Name

Choose an existing variable name. (You may need to define them
first in the Project-->Edit Environment Variables screen.)

Break on Failure

Select

No - Attempt to run the attached component for each iteration, regardless of success or failure.
Yes - If the attached component does not run successfully, fail immediately.
Note: If a failure occurs during any iteration, the failure link is followed. This parameter
controls whether it is followed immediately or after all iterations have been attempted.
Note: This is only available in Sequential mode. When running with concurrency, all iterations will be attempted.

Ordering

Select

If relevant, you may specify sort columns to order the iterations.

Variable Exports

This component makes the following values available to export into variables:

Source

Description

Iteration Attempted

The number of iterations that this component attempts to reach (Max Iterations parameter).

Iteration Generated

The number of iterations that have been initiated. Iterators terminate after failure so this number will be the successful iterations plus any potential failure.

Iteration Successful

The number of iterations successfully performed. This is the max iteration number, minus failures and any unattempted iterations (since the component terminates after failure).

Example

This example iterates through a table of values. For each row of the table, the attached transformation
component runs. The variables are made available to use in that transformation job to
filter the incoming data.

We need to define the variable we intend to iterate, and provide some sensible default values.

In the configuration for the Iterator, an input table is selected. This has columns for Carrier and State.

Those table columns are mapping to the defined variables:

When the transformation runs, the AnalyseFlightData transformation job is executed 4 times, each time
setting the carrier_code and state variables.