Creating and Running an Iterative Job :: SAS(R) Data Integration Studio 4.7: User's Guide

Problem

You want to run a series of similarly structured tables through the same task or series of tasks. For example, you might need to extract specific items of census data from a series of 50 tables. Each table in the series contains data from one of the 50 states in the United States.

Solution

You need to create an iterative job that enables you to run a series of tables through the tasks contained in a job that is placed between Loop and Loop End transformations. This iterative job also contains a control table that lists the tables that are fed through the loop.

Perform the following tasks:

Create the Iterative Job
Variation: Add the Library Input and Library Contents Transformation Directly to a Job
Run the Iterative Job and Examine the Results

Tasks

Create the Iterative Job

Perform the following steps to create and run the iterative job:

Create the control table and the parameterized job that are included in the iterative job. See Creating a Control Table and Creating a Parameterized Job for more information.
Create an empty job.
Select and drag the Loop transformation from the Control folder in the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
Select and drag the control table from its folder. Then, drop it before the Loop transformation on the Diagram tab.
Select and drag the parameterized job from its folder. Then, drop it after the Loop transformation on the Diagram tab.
Select and drag the Loop End transformation from the Control folder in the Transformations tree. Then, drop it after the parameterized job on the Diagram tab.
Drag the control table and connect it to the input port for the Loop transformation.

A sample completed iterative job is shown in the following display.

Completed Iterative Job
Open the Loop Options tab in the properties window for the Loop transformation. Select the Execute iterations in parallel check box. Also select the One process for each available CPU node check box in the Maximum number of concurrent processes group box.
Open the Parameter Mapping tab. Make sure that the appropriate value from the parameterized job is displayed in the Parameter Name column. Then, click the drop-down selection menu in the column for Mapped Source Column. Finally, select the source column that you want to map to the parameter.
Close the properties window for the Loop transformation.

Variation: Add the Library Input and Library Contents Transformation Directly to a Job

You can customize the basic process by adding the library input and the Library Contents transformation directly to an iterative job, as shown in the following example.

Control Table Job in an Iterative Job

When the input library and the Library Contents transformation are added to the iterative job, the contents of the control table are dynamically generated each time that the iterative job is run. This arrangement ensures that the list of tables in the control table is refreshed each time that the job is run. It also ensures that the tables are processed iteratively as each row in the control table is read. For information about control table jobs, see Creating a Control Table.

Run the Iterative Job and Examine the Results

After you run the iterative job, you can find output for the completed iterative processing in the output table for the parameterized job. In addition, the Loop transformation provides a status and run-time information in the temporary output table that is available when it is included in a submitted job. Perform the following steps to run the job, review the status data, and examine the iterative job output:

Run the iterative job. The following display shows a successfully completed sample job.

Sample Successful Iterative Job
Right-click the temporary table that is attached to the Loop transformation and click Open. A sample View Data window for the status information in the Loop transformation temporary output table is shown in the following example.

Loop Transformation Temporary Table

Each row in this table contains information about an iteration in the job.
Double-click the icon for the parameterized job. After the parameterized job opens, right-click the target table icon and click View Data. A sample View Data window for the iterative data is shown in the following example.

View of Target Table Output

Remember that you set a default value for the parameter on the output table when you set up the parameterized job. You can change the default value to see a different portion of the outputted data.