Mappings

Back Next

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.