Mappings

Back Next

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:

Create a mapping.

Create and define the in-out parameter.

Configure the filter condition and source in the Source transformation.

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.
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:
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 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.; You can view the saved expression for VariableMaxDate.

After the mapping runs successfully, the in-out parameter contains the last date for which the task loaded data.