INFORMATICA MAPPING VARIABLES AND PARAMETERS

If you declare mapping parameters and variables in a mapping, you can reuse a mapping by altering the parameter and variable values of the mapping in the session. This can reduce the overhead of creating multiple mappings when only certain attributes of a mapping need to be changed.
The next time you run the session, the Integration Service evaluates references to the variable to the saved value. To override the saved value, define the start value of the variable in a parameter file or assign a value in the pre-session variable assignment in the session properties.

Mapping parameters and variables declared for a mapping cannot be used within a mapplet.
Similarly, you cannot use a mapping parameter or variable declared for a mapplet in a mapping.
When you want to use the same value for a mapping parameter each time you run the session, use the same parameter file for each session run. When you want to change the value of a mapping parameter between sessions you can perform one of the following tasks:

 a)Update the parameter file between sessions.

 b)Create a different parameter file and configure the session to use the new file.

 Remove the parameter file from the session properties. The Integration Service uses the parameter value in the pre-session variable assignment. If there is no pre-session variable assignment, the Integration Service uses the configured initial value of the parameter in the mapping.

For example, you can create a user-defined mapping variable $$LastUpdateDateTime that saves the timestamp of the last row the Integration Service read in the previous session. Use $$LastUpdateDateTime for the beginning timestamp and the built-in variable $$$SessStartTime for the end timestamp in the source filter. Use the following filter to incrementally extract data based on the SALES.sales_datetime column in the source:

SALES.sales_datetime > TO_DATE (‘$$LastUpdateDateTime’) AND SALES.sales_datetime < TO_DATE (‘$$$SessStartTime’)

INITIAL VALUES:
================
String --- Empty string.

Numeric --- 0

Datetime -- 1/1/1753 A.D. or 1/1/1 when the Integration Service is configured for compatibility with 4.0.
======================================================================================================
Variable Values
The Integration Service holds two different values for a mapping variable during a session run:
 Start value of a mapping variable

 Current value of a mapping variable
 
The current value of a mapping variable changes as the session progresses. To use the current value of a mapping variable within the mapping or in another transformation, create the following expression with the SETVARIABLE function:

SETVARIABLE($$MAPVAR,NULL)
At the end of a successful session, the Integration Service saves the final current value of a mapping variable to the repository.

EXAMPLE 1:

1. CREATE A MAPPING WITH TWO PARAMETERS AND ONE VARIABLE
 NAME:$$PAR_INC
 TYPE:PARAMETER
 DATA TYPE: INTEGER
 IS EXPRESSION: FALSE
 SPECIFY: INITIAL VALUE :20000

 NAME:$$PAR_EXP_RATING
 TYPE:PARAMETER
 DATA TYPE: STRING
 LENGTH: 200  --V V IMP
 IS EXPRESSION: TRUE --VERY VERY IMP

 NAME:$$VAR_INC
 TYPE:VARIABLE
 DATA TYPE: INTEGER
 IS EXPRESSION: FALSE
 INITIAL VALUE: 2000
 MAPPING SHOULD BE
 SQ->EXP->TARGET

 SQ--
  SELECT EMP.EID, EMP.ENM, EMP.ELOC, EMP.JDATE, EMP.SAL
  FROM
   EMP
  WHERE SAL>=$$PAR_INC 
   AND JDATE<=TO_DATE (‘$$$SessStartTime’,'mm/dd/yyyy hh24:mi:ss') --USE THE DEFAULT SESSION PARAM
 EXP-->
  SAL_INC-->SAL + $$VAR_INC
  GEN-->STRING (200)-->$$PAR_EXP_RATING

3. GOTO SESSION ->SOURCE CONNECTION->USE VARIABLE->$DBConnection_SRC
    Target Connection->Use variable ->$DBConnection_TGT

4. CREATE A PARAMETER FILE LIKE BELOW

[SUMATHI.s_Map_Var]
$$PAR_INC=30000
$$PAR_EXP_RATING=IIF(SAL<=20000 OR ISNULL(SAL) ,'POOR',IIF(SAL<=30000,'AVG','GOOD'))
$DBConnection_SRC=SUMATHI_ORACLE_CONN
$DBConnection_TGT=SUMATHI_ORACLE_CONN

5. Goto session->properties->specify Param file location
6. Run session