Using Parameters with an SQL Join

Problem

You want to include an SQL Join transformation in a parameterized job that is run in an iterative job. This iterative job contains a control loop in which one or more processes are executed multiple times, so this job needs to be allowed to iteratively run a series of tables in a library through your SQL query. For example, you need to process a series of 50 tables that represent each of the 50 states in the United States through the same SQL query.

Solution

You can create one or more parameters on the Parameters tab in the properties window for the SQL Join transformation. Then, you can use the parameters to tie the SQL Join transformation to the other parts of the parameterized job and the iterative job that contains it. The following prerequisites must be satisfied before the SQL Join transformation can work in this iterative setting:

The SQL Join transformation must be placed in a parameterized job. See Creating a Parameterized Job.
One or more parameters must be set for the input and output tables for the parameterized job. See Set Input and Output Parameters.
One or more parameters must be set for the parameterized job. See Set Parameters for the Job.
The parameterized job must be embedded in an iterative job. See About Iterative Jobs.
The parameters from the parameterized job must be mapped on the Parameter Mapping tab of the properties window for the iterative job.
The tables that you need to process through query created in the SQL Join transformation must be included in the control table for the iterative job. See Creating a Control Table.