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.
Handle incremental data loading for a data warehouse.
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:; When the task runs, Data Integration concatenates the NAME field with 2020.
Parameterize a user-defined function.: Use an in-out parameter to parameterize all or part of a user-defined function in an expression.; Create a string parameter and enable Is expression variable. In the expression, you can use the in-out parameter as a placeholder for the user-defined function or for an argument in the user-defined function. Resolve the parameter at runtime in the mapping task or in the parameter file.

Note: Using in-out parameters in simultaneous mapping task runs can cause unexpected results.

You can use in-out parameters in the following transformations:

•Source
•Target
•Aggregator, but not in expression macros
•Expression, but not in expression macros
•Filter
•Router
•SQL
•Transaction Control

For each in-out parameter you configure the variable name, data type, 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.

In advanced mode, string and text in-out parameter values don't change each time the task runs. The mapping task always uses the same parameter value.

You can't use in-out parameters in mappings in SQL ELT mode.

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:

•Count
•Max
•Min

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 when the mapping runs on the Data Integration Server.	Max or Min	All transformation data types.
SetMaxVariable	Sets the parameter to the maximum value of a group of values. In advanced mode, this function is only available for the Expression transformation.	Max	All transformation data types except string and text data types are available in advanced mode.
SetMinVariable	Sets the parameter to the minimum value of a group of values. In advanced mode, this function is only available for the Expression transformation.	Min	All transformation data types except string and text data types are available in advanced mode.
SetCountVariable	Increments the parameter value by one. In advanced mode, 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. Default is disabled. Not applicable in advanced mode. Mappings in advanced mode resolve the parameter value as a literal string.
Default Value	Optional. Default value for the parameter, which might be the initial value when the mapping first runs. Use one of the following formats for datetime variables: - MM/DD/RR - MM/DD/YYYY - MM/DD/YYYY HH24:MI - MM/DD/RR HH24:MI - MM/DD/RR HH24:MI:SS - MM/DD/YYYY HH24:MI:SS - MM/DD/RR HH24:MI:SS.NS - MM/DD/YYYY HH24:MI:SS.NS
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 (not applicable in advanced mode) - On success - On warning (not applicable in advanced mode) - 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:

•Default Value. The value specified in the in-out parameter configuration.
•Value. The current value of the parameter as the task progresses. When a task starts, the value is the same as the default value. As the task progresses, the task calculates the value using a function that you set for the parameter. The task evaluates the value as each row passes through the mapping. Unlike the default value, the value can change. The task saves the final value in the job details after the task runs.

At run time, the mapping task looks for the value in one of these locations, in the following order:

1Value in the parameter file
2Value saved from the previous task run
3Default value in the mapping
4Default 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:

•When you write expressions that use in-out parameters, you don't need string identifiers for string variables.
•When you use a parameter in a transformation, enclose string parameters in string identifiers, such as single quotation marks, to indicate the parameter is a string.
•When you use in-out parameter in a source filter of type date/time, you must enclose the in-out parameter in single quotes because the value received after Informatica Intelligent Cloud Services resolves the in-out parameter can contain spaces.
•When you use an in-out parameter as a source query, the parameter must contain a valid query as the default value.
•If you copy, import, or export a mapping task, the session values of the in-out parameters are included.
•You can't use in-out parameters in a link rule or as part of a field name in a mapping.
•An in-out parameter name can't be the same as a field name or part of a field name.
•You can't use in-out parameters in an expression macro, because they rely on column names.
•When you use an in-out parameter in an expression or parameter file, precede the parameter name with two dollar signs ($$).
•For some connection types, when you use an in-out parameter for a date/time value, you cannot use $$$SESSSTARTTIME to override the parameter value in a parameter file.

For more information, see the help for the appropriate connector.

•An in-out parameter value can't exceed 4000 characters.
•A mapping task can't resolve a nested in-out parameter when the following conditions are true:

- A parameter file updates an in-out parameter value in a mapping in advanced mode.
- The parameter value is an expression that contains another in-out parameter.

•You can't preview data for sources and transformations with in-out parameters that are in mappings in advanced mode.

Creating an in-out parameter

You can configure an in-out parameter from the Mapping Designer or the Mapplet Designer.

1In the Mapping Designer or Mapplet Designer, add the transformation where you want to use an in-out parameter and add the upstream transformations.
2Open the Parameters panel.

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.

3Add an in-out parameter.
4Configure the parameter properties.
5Use 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:

•View the values of all in-out parameters in the mapping, which can change each time the task runs.
•Reset the configuration to the default values. Click Refresh to reset a single parameter. Click Refresh All to reset all the parameters.
•Edit or change specific configuration details. Click Edit.

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.

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:

•The Source transformation applies 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'

The Source transformation also applies the following sort order to the output to simplify the expression in the next transformation:

users.TIMESTAMP (Ascending)

•The Expression transformation contains a simple expression that sets the current value of $$IncludeMaxDate.

The Expression output field, OutMaxDate, is a string type that enables you to map the expression output to the target.

The New Field dialog box shows the OutMaxDate field with the Output Field field type, string type, and precision of 40.

The SetVariable function sets the current parameter value each time the session runs. For example, if you set the default value of $$IncludeMaxDate to 2016-04-04, the task reads rows dated through 2016-04-04 the first time it runs. The task sets $$IncludeMaxDate to 2016-04-04 when the session is complete. The next time the session runs, the task reads rows with a date greater than 2016-04-04 based on the source filter.

You can view the saved expression for OutMaxDate, which also converts the source column to a DATE_ID in the format YYYY-MM-DD.

•The Target transformation maps the Expression output field to a target column.

When the mapping runs, the OutMaxDate contains the last date for which the task loaded records.

In-out parameter example in advanced mode

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:

1Create a mapping.
2Create and define the in-out parameter.
3Configure the filter condition and source in the Source transformation.
4Add an Expression transformation and configure the SetMaxVariable function.

Create a mapping: Mappings contain the Source transformation and Target transformation by default.; The following image shows a fully configured mapping.
Create and define the in-out parameter: The in-out parameter is a date field where you want to use the MM/DD/YYYY HH24:MI:SS.NS format.; In the Mapping Designer, open the Parameters panel and configure an in-out parameter as shown in the following image:
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:
Add an Expression transformation and configure the SetMaxVariable function: The Expression transformation contains a simple expression that sets the current value of $$IncludeMaxDate.; You can view the saved expression for VariableMaxDate.

Using in-out parameters as expression variables

Configure an in-out parameter as an expression variable and use it to parameterize expressions or user-defined functions. To use an in-out parameter as an expression variable, create a string parameter and enable the Is expression variable option. You can't use an in-out parameter as an expression variable in advanced mode.

When you enable Is expression variable, 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:

•Aggregator
•Expression
•Filter
•Router

You can override the parameter at runtime with a value specified in a parameter file.

1In the mapping, create an in-out parameter.

2Configure the parameter properties.

3Set the data type to String or Text.

4Enable the Is expression variable option.

5Use the parameter as an expression or user-defined function.

6Optionally, you can override the default value of the parameter in one of the following places:

- On the In-Out Parameters tab of the task.

- In a parameter file. Enter the parameter file name and location on the Runtime Options tab of the task.

When the task runs, Data Integration resolves the parameter as an expression.

Using in-out parameters as source queries

You can use an in-out parameter as a placeholder for a source query. To use an in-out parameter as a source query, create a string parameter, define a default query value, and enter the parameter in the query dialog of the Source transformation properties.

When you use an in-out parameter as a source query, you must provide a valid query for the default parameter value. Data Integration uses the default query to generate design time source metadata. Data Integration uses this metadata to map fields to the fields in the query that you use at run time. If Data Integration can't generate the design time metadata, the task fails.

You can create the in-out parameter before or after you configure the Source transformation. However, if you configure the Source transformation before you define the default parameter value, you must synchronize the source fields on the Fields tab before you run the task.

1In the Mapping Designer, create an in-out parameter and enter a default query.

2Configure the Source transformation.

3Select Query as the source type and enter the in-out parameter in the Query dialog using the following format:

$$<parameter name>

4In the mapping task, perform one of the following actions:

- On the Source tab, enter the query that you want to use at run time.
- On the Schedule or Runtime Options tab, enter the parameter file details.