Table of Contents

Search

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

Mappings

Mappings

In-out parameter example

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.
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:
  • 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.

0 COMMENTS

We’d like to hear from you!