In the Mapping Designer, using mapping parameters and variables (will be explained in next post) to make mappings more flexible. Mapping parameters and variables represent values in mappings and
mapplets. If mapping parameters and variables declared in a mapping, mappings can be reused by altering the parameter and variable values in the parameter file. This can reduce the overhead of redeveloping of the mappings when only certain attributes of a mapping need to be changed.
When you use a mapping parameter or variable in a mapping, first you declare the mapping parameter or variable for use in each mapplet or mapping. Then, you define a value for the mapping parameter or variable in the parameter file before you run the session. For an example, use mapping parameters in the source filter or SQL override of a Source Qualifier transformation to determine the load date in the table.
Table.Load_Date = TO_DATE('$$Run_Date', 'MM/DD/YYYY')
Let's see the use of Mapping parameters.
A mapping parameter represents a constant value that is defined before running a session. A mapping parameter retains the same value throughout the entire session. Declare and create a mapping parameter in the mapping, and then define the value of the parameter in a parameter file before running the session. The Integration Service evaluates all references to the parameter to that value.
Mapping parameter can be created in the Mapping Designer's -> Mapping menu -> Parameters and Variables.
Sample of a parameter file and its entries:
[session_name]
$$Run_Date=02/01/2016
Defined parameter can be used in the SQL query, user-defined join, source filter, and pre- and post-session SQL commands of a Source Qualifier transformation. The Integration Service first generates an SQL query and expands each parameter or variable. It replaces each mapping parameter, mapping variable, and workflow variable with its start value. Then it runs the query on the source database.
Tip: To ensure the format of a date-time parameter or variable matches that used
by the source, validate the SQL query.
Once parameters declared and created, mapping parameters appear in the Variables tab of the Expression Editor. Use them in any expression in the mapplet or mapping. The Designer validates mapping parameters and variables in the Expression Editor of mapplets and mappings, without the single quotes. A mapping parameter $$Target_Table is used as '$$Target_Table' in an expression editor then Integration Service reads it as $$Target_Table literal and do not attempt to resolve the value of the parameter however while using it in the SQL override remember to use it with quotes.
Mapping Parameters Initial and Default Values
When a mapping parameter is declared in a mapping or a mapplet, you can enter an initial value. The Integration Service uses the configured initial value for a mapping parameter when the parameter is not defined in the parameter file. The Integration Service uses Default value when no value is declared in the parameter file and even initial value is also missing.
Default Values for Mapping Parameters and Variables Based on
|
Datatype
|
Data Default Value
|
|
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
|
To use a mapping parameter, complete the following steps:
1. Create a mapping parameter.
2. Use the mapping parameter.
3. Define the parameter value.
Step 1. Create a Mapping Parameter
You can create mapping parameters for any mapping or mapplet. You can
declare as many mapping parameters as you need. Once declared, use the
parameter in the mapping or mapplet.
To create a mapping parameter:
1. In the Mapping Designer, click Mappings > Parameters and Variables.
-or-
In the Mapplet Designer, click Mapplet > Parameters and Variables.
2. Click the Add button.
3. Enter the following information and click OK:
Options for Declaring Mapping Parameters
Parameter name: $$ParameterName. (The syntax for the parameter name must be $$ followed by any alphanumeric or underscore characters)
Select Variable or parameter: Select Parameter
Datatype (Datatype of the parameter.) Select a valid transformation datatype.
Use any datatype except Binary or Raw.
Precision or Scale: Precision and scale of the parameter.
Aggregation (Use only for variables)
IsExprVar: Determines how the Integration Service 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.
Initial Value Initial value of the parameter. If you do not set a value for the parameter in the parameter file, the Integration Service uses this value for the parameter during sessions. If this value is also
undefined, the Integration Service uses a default value based on the datatype of the mapping variable.
Use any of the following formats for initial values for Date/Time parameters:
-
MM/DD/RR
-
MM/DD/RR HH24:MI:SS
-
MM/DD/YYYY
-
MM/DD/YYYY HH24:MI:SS.US
Step 2. Use a Mapping Parameter
After you create a parameter, use it in the Expression Editor of any transformation in a mapping or mapplet. You can also use it in Source Qualifier transformations and reusable transformations.
In a Source Qualifier transformation, mapping parameters appear on the Variables tab in the SQL Editor. Use the following rules and guidelines when you use mapping parameters in a Source Qualifier transformation:
Enclose string parameters in string identifiers appropriate to the source system. When necessary, change the format of the datetime parameter to match the format in the source. You can also use mapping parameters in the Expression Editor. When using mapping parameters in the Expression Editor, do not enclose string parameters in string identifiers. The Integration Service handles parameters just like any other port identifiers.
Use mapping parameters in reusable transformations.
You can also use mapping parameters in transformation overrides in the session properties in the Workflow Manager. You can override properties such as a filter or user-defined join in a Source Qualifier transformation.
Step 3. Define a Parameter Value
Before you run a session, define values for mapping parameters in the parameter file. When you do not define a parameter in the parameter file, the Integration Service gets the parameter value from another place. The Integration Service looks for the value in the following order:
1. Value in parameter file
2. Value in pre-session variable assignment
3. Value saved in the repository
4. Initial value
5. Datatype default value
