Parameters and variables in Informatica PowerCenter

Parameters and Variables are used to store information during the execution. Each parameter and variable is defined with a specific data type and their main purpose is to provide increased development flexibility. 

The value of a parameter is fixed during the run of the mapping

Variables can change in value during run-time 

   The format for declaring variables and parameters is $$VariableName or $$ParameterName .         

Steps to create parameters and variables -->

Step 1 – To Create mapping parameter - In mapping designer,

1. Select mappings menu
2. Select parameters and variables menu

Step 2- In next screen,

1. Click on add new variable menu
2. From drop down, select type as parameter
3. Enter parameter name as $$Deptno
4. Enter an initial value of 10
5. Select OK button

Now, you have created a mapping parameter deptno, with initially assigned value of 10, and this parameter can be referenced inside the mapping.

How to Create Mapping Variable

Step 1 – In mapping designer

1. Select mappings menu
2. Select parameters and variables menu

Step 2- On the next screen

1. Click on add new variable menu
2. From drop down, select type as variable
3. Enter variable name as $$TotalSalary
4. Select DataType as decimal
5. Enter an initial value of 0
6. Select OK button

This will create a mapping variable.

Note – mapping parameter and variable names always begin with $$. 

To change the value of a variable, one of the following functions can be used within an expression: 

SETMAXVARIABLE($$Variable, value) , SETMINVARIABLE($$Variable, value), SETVARIABLE($$Variable, value) , SETCOUNTVARIABLE($$Variable), where

SETVARIABLE sets the variable to a value that you specify (executes only if a row is marked as insert or update). At the end of a successful session, the Integration Service saves either the MAX or MIN of (start value.final value) to the repository, depending on the aggregate type of the variable. Unless overridden, it uses the saved value as the start value of the variable for the next session run.

SETCOUNTVARIABLE - increments a counter variable. If the Row Type is Insert increment +1, if Row Type is Delete increment -1. A value = 0 is used for Update and Reject.

SETMAXVARIABLE - compare current value to value passed into the function. Returns the higher value and sets the current value to the higher value.

SETMINVARIABLE - compare current value to the value passed into the function. Returns the lower value and sets the current value to the lower value

• Note --> At the end of a successful session, the values of variables are saved to the repository. The SetVariable function writes the final value of a variable to the repository based on the Aggregation Type selected when the variable was defined.

• Initialization priority of Parameters:           Parameter file, Declared initial value, Default value
• Initialization priority of Variables:             Parameter file, Repository value, Declared initial value, Default value