Advance topic on Mapping Parameters and Variables

Advance topic on Mapping Parameters and Variables:

The Mapping parameters and variables are used to make the mappings more flexible/dynamic. Mapping parameters and variables represent values in mappings and mapplets.

If we declare mapping parameters and variables in a mapping, we can reuse a mapping by changing the parameter and variable values of the mapping in the session or through a parameter file. This will reduce the overhead of creating multiple mappings when only certain values of a mapping are different.

When we create a parameter or variable in a mapping/mapplet, then we need to define a value for that mapping parameter or variable in a parameter file before you run that session.

Mapping Parameter: It represents a constant value and retains the same value throughout the session that we define before running a session.

Mapping Variable: It represents a value that can change throughout the session based on the business logic define for the mapping variable in the mapping. The Integration Service saves that value of a mapping variable to the repository at the end of the each successful session run and uses that value the next time you run the session.

Note: If you don’t want to use the saved value of the mapping variable at the end of the session. You can override those values at the end of the session.

Mapping Parameter declaration:

Name: $$NEWVARIABLE (starts with $$)

Type: Parameter

Datatype: string/text/small integer/real/ntext/nstring/integer/double/decimal/datetime

Precision: as you required

Scale: as you required

Aggregation: NA

IsExpVar: FALSE/TRUE.

What is “IsExpVar”: IsExpVar is determines how the Integration Service will expands the parameter in an expression string.

• If true, the Integration Service expands the parameter before parsing the expression.

• If false, the Integration Service expands the parameter after parsing the expression.

• Default is false.

Note: If you set this field to true, you must set the parameter datatype to String, or the Integration Service fails the session. 

Mapping Variable declaration:

Name: $$NEWVARIABLE (starts with $$)

Type: Variable

Datatype: string/text/small integer/real/ntext/nstring/integer/double/decimal/datetime

Precision: as you required

Scale: as you required

Aggregation: Max/Min and Count for integer datatype.

IsExpVar: FALSE/TRUE.

 

Aggregation: Determines the type of calculation you can perform with the variable.

• Set the aggregation to Max if you want to use the mapping variable to determine a maximum value from a group of values.

• Set the aggregation to Min if you want to use the mapping variable to determine a minimum value from a group of values.

• Set the aggregation to Count if you want to use the mapping variable to count number of rows read from source.

Note: The Integration Service saves that value of a mapping variable to the repository at the end of the each successful session run and uses that value the next time you run the session.

Here is the sample mapping for how to use mapping variables of different types.

1. First Drag & Drop Source/Target definition into mapping designer workspace.

2. From the menu select: Mappings à Parameters and Variables

 

• Added 3 ports
• Name them $$Max_Value, $$Min_Value and $$Count_Value
• Select Type as Variable
• Datatype as integer
• Aggregation: $$Max_Value as Max, $$Min_Value as Min and $$Count_Value as Count

3. Added an Expression Transformation in between Source Qualifier and Target Instance.

4. Drag all port from SQ to Expression Transformation.

5. Select Expression and Edit it.

Added 3 output ports:

out_MAX_SALARY = SETMAXVARIABLE($$Max_Value, SAL)

out_MIN_SALARY = SETMINVARIABLE($$Min_Value, SAL)

out_Count_Variable = SETCOUNTVARIABLE($$Count_Value)

OR

3 variable ports and 3 port ports:

var_MAX_SALARY = SETMAXVARIABLE($$Max_Value, SAL)

out_MAX_SALARY = var_MAX_SALARY

var_MIN_SALARY = SETMINVARIABLE($$Min_Value, SAL)

out_MIN_SALARY = var_MIN_SALARY

var_Count_Variable = SETCOUNTVARIABLE($$Count_Value)

out_Count_Variable = var_Count_Variable

6. Map the ports from Expression to Target as below: