Mappings > Parameters > In-out parameters
  

In-out parameters

An in-out parameter is a placeholder for a value that stores a counter or task stage. Data Integration evaluates the parameter at run time based on your configuration.
In-out parameters act as persistent task variables. The parameter values are updated during task execution. The parameter might store a date value for the last record loaded from a data warehouse or help you manage the update process for a slowly changing dimension table.
For example, you might use an in-out parameter in one of the following ways:
Update values after each task execution.
You can use the SetVariable, SetMaxVariable, SetMinVariable, or SetCountVariable function in an Expression transformation to update parameter values each time you run a task.
To view the parameter values after the task completes, open the job details from the All Jobs or My Jobs page. You can also get these values when you work in the Mapping Designer or through the REST API.
Handle incremental data loading for a data warehouse.
In this case, you set a filter condition to select records from the source that meet the load criteria. When the task runs, you include an expression to increment the load process. You might choose to define the load process based on one of the following criteria:
Parameterize an expression.
You might want to parameterize an expression and update it when the task runs. Create a string or text parameter and enable Is expression variable. Use the parameter in place of an expression and resolve the parameter at run time in a parameter file.
For example, you create the expression field parameter $$param and override the parameter value with the following values in a parameter file:
$$param=CONCAT(NAME,$$year)
$$year=2020
When the task runs, Data Integration concatenates the NAME field with 2020.
Note: Using in-out parameters in simultaneous mapping task runs can cause unexpected results.
You can use in-out parameters in the following transformations:
For each in-out parameter you configure the variable name, datatype, default value, aggregation type, and retention policy. You can also use a parameter file that contains the value to be applied at run time. For a specific task run, you can change the value in the mapping task.
Unlike input parameters, an in-out parameter can change each time a task runs. The latest value of the parameter is displayed in the job details when the task completes successfully. The next time the task runs, the mapping task compares the in-out parameter to the saved value.
You can also reset the in-out parameters in a mapping task, and then view the saved values in the job details.

Aggregation types

The aggregation type of an in-out parameter determines the final current value of the parameter when the task runs. You can use variable functions with a corresponding aggregation type to set the parameter value at run time.
You can select one of the following aggregation types for each parameter:

Variable functions

Variable functions determine how a task calculates the current value of an in-out parameter at run time.
You can use variable functions in an expression to set the current parameter value when a task runs.
To keep the parameter value consistent throughout the task run, use a valid aggregation type in the parameter definition. For example, you can use the SetMaxVariable function with the Max aggregation type but not the Min aggregation type.
The following table describes the available variable functions, aggregation types, and data types that you use with each function:
Variable function
Description
Valid aggregation type
Valid data type
SetVariable
Sets the parameter to the configured value. At the end of a task run, it compares the final current value to the start value. Based on the aggregation type, it saves a final value in the job details.
This function is only available for non-elastic mappings.
Max or Min
All transformation data types except string and text data types are available for elastic mappings.
SetMaxVariable
Sets the parameter to the maximum value of a group of values.
In an elastic mapping, this function is only available for the Expression transformation.
Max
All transformation data types except string and text data types are available for elastic mappings.
SetMinVariable
Sets the parameter to the minimum value of a group of values.
In an elastic mapping, this function is only available for the Expression transformation.
Min
All transformation data types except string and text data types are available for elastic mappings.
SetCountVariable
Increments the parameter value by one.
In an elastic mapping, this function is only available for the Expression transformation. Configure the SetCountVariable function immediately before the target transformation to avoid a non-deterministic COUNT return value. For example, if you configure the SetCountVariable function before a transformation that contains multiple downstream pipelines, the generated COUNT value might be n times the actual row count.
Count
Integer and bigint
Note: Use variable functions one time for each in-out parameter in a pipeline. During run time, the task evaluates each function as it encounters the function in the mapping. As a result, the task might evaluate functions in a different order each time the task runs. This might cause inconsistent results if you use the same variable function multiple times in a mapping.

In-out parameter properties

Specify the parameter properties for each in-out parameter that you define.
The following table describes the in-out parameter properties:
In-out parameter property
Description
Name
Required. Name of the parameter.
The parameter name cannot contain the text strings CurrentTaskName, CurrentTime, LastRunDate, or LastRunTime.
Description
Optional. Description that is displayed with the parameter in the job details and the mapping task.
Maximum length is 255 characters.
Data Type
Required. Data type of the parameter.
Note: Select a compatible aggregation type. For example, if you select string, you cannot configure it with the Count aggregation type.
Precision
Required. Precision of the parameter.
Scale
Optional. Scale of the parameter.
Is expression variable
Optional. Controls whether Data Integration resolves the parameter value as an expression.
Disable to resolve the parameter as a literal string.
Applicable when the data type is String or Text.
Not available for elastic mapping.
Default is disabled.
Default Value
Optional. Default value for the parameter, which might be the initial value when the mapping first runs.
Use the following format for default values for datetime variables: MM/DD/YYYY HH24:MI:SS.US.
Retention Policy
Required. Determines when the mapping task retains the current value, based on the task completion status and the retention policy.
Select one of the following options:
  • - On success or warning (available for non-elastic mappings)
  • - On success
  • - On warning (available for non-elastic mappings)
  • - Never
Aggregation Type
Required. Aggregation type of the variable. Determines the type of calculation you can perform and the available variable functions.
Select one of the following options:
  • - Count to count number of rows read from source.
  • - Max to determine a maximum value from a group of values.
  • - Min to determine a minimum value from a group of values.

In-out parameter values

An in-out parameter is a placeholder for a value or values that the task applies at run time. You define the value of the in-out parameter in the mapping and you can edit the value when you configure the mapping task.
A mapping task uses the following values to evaluate the in-out parameter at run time:
At run time, the mapping task looks for the value in one of these locations, in the following order:
  1. 1. Value in the parameter file
  2. 2. Value saved from the previous task run
  3. 3. Default value in the mapping
  4. 4. Default value for the data type
If you want to override a saved value, define a value for the in-out parameter in a parameter file. The task uses the value in the parameter file.

Rules and guidelines for in-out parameters

Consider the following rules and guidelines:

Creating an in-out parameter

You can configure an in-out parameter from the Mapping Designer or the Mapplet Designer.
  1. 1. In the Mapping Designer or Mapplet Designer, add the transformation where you want to use an in-out parameter and add the upstream transformations.
  2. 2. Open the Parameters panel.
  3. The In-Out Parameters display beneath the Input Parameters.
    When you click the Parameters icon, a Parameters panel appears with sections for input parameters and in-out parameters. In this example, there are no in-out parameters so the panel states there are no in-out parameters. You can click the Add icon to add an in-out parameter.
  4. 3. Add an in-out parameter.
  5. 4. Configure the parameter properties.
  6. 5. Use the parameter as a variable in the transformation where you want to set the value when the mapping runs.
For details on the in-out parameter properties and the Parameters panel, see In-out parameter properties and Mapping Designer.

Editing in-out parameters in a mapping task

An in-out parameter is a placeholder for a value in a mapping. The task determines the value to apply during run time. You configure an in-out parameter in the mapping and can edit the value in the mapping task.
When you deploy a mapping that includes an in-out parameter, the task sets the parameter value at run time based on the parameter's retention policy. By default, the mapping task retains the value set during the last session. If needed, you can reset the value in the mapping task.
From the mapping task wizard, you can perform the following actions for in-out parameters:
For example, the following image shows configuration details of the "Timestamp" parameter and the value at the end of the last session:
The mapping task wizard displays the in-out parameters.

View in-out parameters in the job details

To find the value of an in-out parameter after a task runs, view the job details. To view job details, open Monitor and select All Jobs or open Data Integration and select My Jobs. Then click the job name.
The following image shows an example of the available details, including the current value of the specified parameter, set during the last run of a mapping task:
The "In-Out Parameters" area at the bottom of the job details shows the values for each in-out parameter in the mapping.
The in-out parameters appear in the job details based on the retention policy that you set for each parameter.

In-out parameter example

You can use an in-out parameter as a persistent task variable to manage an incremental data load.
The following example uses an in-out parameter to set a date counter for the task and perform an incremental read of the source. Instead of manually entering a task override to filter source data each time the task runs, the mapping contains a parameter, $$IncludeMaxDate.
In the example shown here, the in-out parameter is a date field where you want to support the MM/DD/YYYY format. To support this format, you can use the SetVariable function in the Expression transformation and a string data type.
Note: You can also configure a date/time data type if your source uses a date format like YYYY-MM-DD HH:MM:SS. In that case, use the SetMaxVariable function.
In the Mapping Designer, you open the Parameters panel and configure an in-out parameter as shown in the following image:
This in-out parameter, IncludeMaxDate, has a string data type, precision of 40 and a default vault of 2016-01-01. Retention Policy is On success or warning and Aggregation Type is Max.
The sample mapping has the following transformations:
When the mapping runs, the OutMaxDate contains the last date for which the task loaded records.

In-out parameter example for elastic mappings

You can use an in-out parameter as a persistent task variable to manage an incremental data load.
The following example uses an in-out parameter to set a date counter for the task and perform an incremental read of the source. Instead of manually entering a task override to filter source data each time the task runs, the mapping contains a parameter, $$IncludeMaxDate. This example is based on a relational database source with an incremental timestamp column.
The high level steps is this example include:
  1. 1. Create a mapping.
  2. 2. Create and define the in-out parameter.
  3. 3. Configure the filter condition and source in the Source transformation.
  4. 4. Add an Expression transformation and configure the SetMaxVariable expression.
Create a mapping
Mappings contain the Source transformation and Target transformation by default.
The following image shows a fully configured mapping.
This mapping contains a Source transformation, an Expression transformation, and a Target transformation.
Create and define the in-out parameter
The in-out parameter is a date field where you want to support the MM-DD-YYYY HH24:MM:SS.US format. To support this format, you can use the SetMaxVariable function in the Expression transformation and a date/time data type.
In the Mapping Designer, open the Parameters panel and configure an in-out parameter as shown in the following image:
This in-out parameter has the date/time data type, precision of 29, retention policy of On success, and aggregation type of Max.
Configure the filter condition and source in the Source transformation
Use the Source filtering options in the Source transformation to apply the following filter to select rows from the users table where the transaction date, TIMESTAMP, is greater than the in-out parameter, $$IncludeMaxDate:
users.TIMESTAMP > '$$IncludeMaxDate'
Add an Expression transformation and configure the SetMaxVariable expression
The Expression transformation contains a simple expression that sets the current value of $$IncludeMaxDate.
The New Field dialog box shows the Field Type as Variable Field, Name as VariableMaxDate, Type as date/time, and Precision as 29.
The New Field dialog box shows the Field Type value of Variable Field, Name value of VariableMaxDate, Type value of date/time, Precision value of 29, and Scale value of 9.
The SetMaxVariable function sets the current parameter value each time the task runs. For example, if you set the default value of $$IncludeMaxDate to 04-04-2020 10:00:00, the task reads rows dated through 04-04-2020 the first time it runs. For the first task run, you specify the start date based on your needs. The task sets $$IncludeMaxDate to 11-04-2020 10:00:00 when the session is complete. The next time the task runs, it reads rows with a date/time value greater than 11-04-2020 10:00:00 based on your configuration of the Source filtering options.
The SetMaxVariable function reads as SetMaxVariable($$IncludeMaxDate, TIMESTAMP).
You can view the saved expression for VariableMaxDate.
The saved expression for VariableMaxDate is available for editing on the Expression page.
After the mapping runs successfully, the in-out parameter contains the last date for which the task loaded data.

Using in-out parameters as expression variables

In a non-elastic mapping, you can use an in-out parameter as a placeholder for an expression. To use an in-out parameter as an expression, create a string parameter and enable the Is expression variable option. You cannot use an in-out parameter as an expression in an elastic mapping.
When you enable this option, Data Integration resolves the parameter as an expression. When you disable this option, Data Integration resolves the parameter as a literal string.
You can use an in-out parameter as an expression variable in the following transformations:
You can override the parameter at runtime with a value specified in a parameter file.
    1. In the mapping, create an in-out parameter.
    2. Configure the parameter properties.
    3. Set the data type to String or Text.
    4. Enable the Is expression variable option.
    5. Use the parameter as an expression.
    6. Optionally, you can override the default value of the parameter in one of the following places:
When the task runs, Data Integration resolves the parameter as an expression.