Table of Contents

Search

  1. Preface
  2. Mappings
  3. Parameters
  4. CLAIRE recommendations
  5. Data catalog discovery
  6. Visio templates

Mappings

Mappings

In-out parameter example for elastic mappings

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. Create a mapping.
  2. Create and define the in-out parameter.
  3. Configure the filter condition and source in the Source transformation.
  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.