PowerCenter - Is the variable an expression ? (IsExprVar)

About

IsExprVar is a propertie thay you must set when you define a mapping parameter or variable.

It determines how the Integration Service expands (replaces) them in an expression string when you run a session.

The default value is false which means that if you use a mapping parameter or variable in an expression, the Integration Service expands the parameter or variable after it parses the expression.

But you might want the Integration Service to expand a parameter or variable before it parses the expression when you create expressions to represent business rules that change frequently.

Then:

• Set this field to true (the parameter datatype must be String, or the Integration Service fails the session).
• and define the expressions in a parameter file so you do not have to change the mappings every time the business rules change.

Articles Related

Example

You have an expression that generates a color name based on an ID string as follows:

IIF(color=‘A0587’,‘white’)

The next month, you modify the expression as follows:

IIF(color=‘A0587’,‘white’,IIF(color=‘A0588’,‘off white’))

Instead of updating the mappings that use this expression every time the business rule changes, you can define the expression in a parameter file and update the file when the expression changes.

To define an expression in a parameter file, set up the mapping and workflow as follows:

• Create a mapping parameter or variable to store the color name expression. For example, create a mapping parameter, $ExpColor.
• For mapping parameter $ExpColor, set the IsExprVar property to true. You must also set the datatype for the parameter to String or the Integration Service fails the session.
• In the Expression transformation, set the output port to the following expression: $ExpColor
• Configure the session or workflow to use a parameter file.
• In the parameter file, set $ExpColor to the correct expression. For example:

$$ExpColor=IIF(color=‘A0587’,‘white’)

Because IsExprVar for mapping parameter $ExpColor is set to true, the Integration Service expands the parameter before it parses the expression. Rows with color ID “A0587” return the string “white.”.

If IsExprVar is set to false, the Integration Service expands the parameter after it parses the expression. Therefore, all rows return the string

IIF(color=‘A0587’,‘white’)

When the color name expression changes, you can update the value of the mapping parameter in the parameter file. You do not need to modify the mapping.

Documentation / Reference

• Defining Expression Strings in Parameter Files in PowerCenter Design Guide (page 151)