Parameter File

Overview:

A parameter file is a list of parameters and variables and their associated values. These values are defined properties for a service, service process, workflow, worklet, or session. The Integration Service applies these values when you run a workflow or session that uses the parameter file.

Note: Parameters and Variables defined at mapping level are initialized through the session at the run time.

Parameter files provide the flexibility to changing parameter and variable values each time you run a session or workflow. You can include information for multiple services, service processes, workflows, worklets, and sessions in a single parameter file. You can also create multiple parameter files and use a different file each time you run a session or workflow.

The Integration Service reads the parameter file at the start of the workflow or session to determine the start values for the parameters and variables defined in the file. You can create a parameter file using a text editor such as WordPad or Notepad or EditPlus or Notepad ++.

Below is the following information when you use parameter files:

• Types of parameters and variables: You can define different types of parameters and variables in a parameter file. These include service variables, service process variables, workflow and worklet variables, session parameters, and mapping parameters and variables.
• Properties you can set in parameter files: You can use parameters and variables to define many properties in the Designer and Workflow Manager. The Integration Service expands the parameter when the session runs.
• Parameter file structure: You can assign a value for a parameter or variable in the parameter file by entering the parameter or variable name and value on a single line in the form name=value. Groups of parameters and variables must be preceded by a heading that identifies the service, service process, workflow, worklet, or session to which the parameters or variables apply.
• Parameter file location: You can specify the parameter file to use for a workflow or session. You can enter the parameter file name and directory in the workflow or session properties or in the pmcmd command line for manual.

Note:

Parameters can be defined in global or local section. The Parameter defined under global can be used in any of the session defined in that parameter file. Like database connection, database user name, database password etc. The parameters defined under session are application for only that particular session only. We will be see this more at below.

Parameter and Variable Types

A parameter file can contain different types of parameters and variables. When you run a session or workflow that uses a parameter file, the Integration Service reads the parameter file and expands the parameters and variables defined in the file.

We can define the following types of parameter and variable in a parameter file:

• Service variables: It defines general properties for the Integration Service such as email addresses, log file counts, and error thresholds. Example: $PMSuccessEmailUser, $PMSessionLogCount, and $PMSessionErrorThreshold. The service variable values you define in the parameter file override the values that are set in the Administrator tool.
• Service process variables: Itdefines the directories for Integration Service files for each Integration Service process. Example: $PMRootDir, $PMSessionLogDir, and $PMBadFileDir, $PMSourceFileDir, $PMTargetFileDir. The service process variable values you define in the parameter file override the values that are set in the Administrator tool. If the Integration Service uses operating system profiles, the operating system user specified in the operating system profile must have access to the directories you define for the service process variables.
• Workflow variables: It evaluates task conditions and record information in a workflow. Example: you can use a workflow variable in a Decision task to determine whether the previous task ran properly. In a workflow, $TaskName.PrevTaskStatus is a predefined workflow variable and $$VariableName is a user-defined workflow variable.
• Worklet variables: It evaluates task conditions and record information in a worklet. You can use predefined worklet variables in a parent workflow, but you cannot use workflow variables from the parent workflow in a worklet. In a worklet, $TaskName.PrevTaskStatus is a predefined worklet variable and $$VariableName is a user-defined worklet variable.
• Session parameters: It definesvalues that can change from session to session, such as database connections or file names. $PMSessionLogFile and $ParamName are user-defined session parameters.
• Mapping parameters: It defines values that remain constant throughout a session, such as state sales tax rates. When declared in a mapping or mapplet, $$ParameterName is a user-defined mapping parameter.
• Mapping variables: It defines values that can change during a session. The Integration Service saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time you run the session. When declared in a mapping or mapplet, $$VariableName is a mapping variable.

We cannot define the following types of variables in a parameter file:

• ·         $Source and $Target connection variables: Define the database location for a relational source, relational target, lookup table, or stored procedure.
  ·         Email variable: Define session information in an email message such as the number of rows loaded, the session completion time, and read and write statistics.
  ·         Local variables: Temporarily store data in variable ports in Aggregator, Expression, and Rank transformations.
  ·         Built-in variables: Variables that return run-time or system information, such as Integration Service name or system date.
  ·         Transaction control variables: Define conditions to commit or rollback transactions during the processing of database rows.
  ·         ABAP program variables: Represent SAP structures, fields in SAP structures, or values in the ABAP program.

Parameter File Structure

A parameter file contains a list of parameters and variables with assigned values. You group parameters and variables in different sections of the parameter file. Each section is preceded by a heading that identifies the Integration Service, Integration Service process, workflow, worklet, or session to which you want to define parameters or variables. You define parameters and variables directly below the heading, entering each parameter or variable on a new line. You can list parameters and variables in any order within a section.

Enter the parameter or variable definition in the form name=value.

Example, the following lines assign a value to InputFile, OutputFile

$InputFileName=sample_source.txt

$OutputFileName=sample_target.txt

The Integration Service interprets all characters between the beginning of the line and the first equals sign as the parameter name and all characters between the first equals sign and the end of the line as the parameter value. Therefore, if you enter a space between the parameter name and the equals sign, the Integration Service interprets the space as part of the parameter name. If a line contains multiple equals signs, the Integration Service interprets all equals signs after the first one as part of the parameter value.

Warning: The Integration Service uses the period character (.) to qualify folder, workflow, and session names when you run a workflow with a parameter file. If the folder name contains a period (.), the Integration Service cannot qualify the names properly and fails the workflow.

Parameter File Sections

We can define parameters and variables in any section in the parameter file. If you define a service or service process variable in a workflow, worklet, or session section, the variable applies to the service process that runs the task. Similarly, if you define a workflow variable in a session section, the value of the workflow variable applies only when the session runs.

The following table describes the parameter file headings that define each section in the parameter file and the scope of the parameters and variables that you define in each section:

Sample Parameter File:

[Global]

The global parameter and variable can be used any of the session, worklet or workflow mentioned in that parameter file without repeating them again and again.

[<folder name>.WF:<workflowname>.WT:<workletname>.ST:<session name>]

Note: [] will defined the entry for the respective job details like

Folder Name=you project folder

WF: is to identify the workflow name followed by wokflowname

WT: is to identify the worklet name followed by workletname

ST: is to identify the session name followed by sessionname

Below example for session details of the workflow in particular folder

[<folder name>.WF:<workflowname>.ST:<session name>]

Below example for session in a particular folder.

[<folder name>.<session name>]

Below example is global session which can be in worklet/workflow/folder in that particular repository.

[<session name>]

Example:

[Global]

$$SourceSystem=’Hyderabad’

$$ETLUSER=’gk1’

$$LOADTYPE=’Adhoc’

$DBConnection_Oracle=Scott

[Practice.WF:wf_s_m_employee_table2file.ST:s_m_employee_table2file]

$$LastRunDate=12/31/2013 01:12:11

$$Departments=(10,20)

$$Region=’INDIA’

Note: $ is defined session variable, $$ mapping variable/parameters and $$$ are pre-defined (default) variable like $$$SessStartTime

The variable defined under global can be used in any of the workflow mentioned in that same parameter file.

Comments

You can include comments in parameter files. The Integration Service ignores lines that are not valid headings and do not contain an equals sign character (=). The following lines are examples of parameter file comments:

---------------------------------------

Created 10/11/12 by xyz.

*** Update the parameters below this line when you run this workflow on Integration Service Int_01. ***

; This is a valid comment because this line contains no equals sign.

Null Values

You can assign null values to parameters and variables in the parameter file. When you assign null values to parameters and variables, the Integration Service obtains the value from the following places, depending on the parameter or variable type:

• Service and service process variables: The Integration Service uses the value set in the Administrator tool.
• Workflow and worklet variables: The Integration Service uses the value saved in the repository (if the variable is persistent), the user-specified default value, or the datatype default value.
• Session parameters: Session parameters do not have default values. If the Integration Service cannot find a value for a session parameter, it may fail the session, take an empty string as the default value, or fail to expand the parameter at run time. For example, the Integration Service fails a session where the session parameter $DBConnectionName is not defined.
• Mapping parameters and variables: The Integration Service uses the value saved in the repository (mapping variables only), the configured initial value, or the datatype default value.

To assign a null value, set the parameter or variable value to “<null>” or leave the value blank. For example, the following lines assign null values to service process variables $PMBadFileDir and $PMCacheDir:

$PMBadFileDir=<null>

$PMCacheDir=

Where to Use Parameters and Variables

We can use parameters and variables to assign values to properties in the Designer and Workflow Manager and to override some service and service process properties.

Example:

1. We can use a parameter to specify the Source/Target/Lookupfile name with folder path.
2. We can use a parameter to specify the relation Source/Target/Lookup table name and schema name.

If the property is a SQL statement or command, we can either use parameters and variables within the statement or command, or we can enter a parameter or variable in the input field for the property, and set the parameter or variable to the entire statement or command in the parameter file.

Example: We want to use a parameter or variable in a relational target override. We can enter a parameter or variable within the UPDATE statement of a relational target override and define the parameter or variable below the appropriate heading in the parameter file. Or, to define the UPDATE statement in a parameter file, complete the following steps:

1. In the Designer, edit the target instance, enter session parameter $ParamMyOverride in the Update Override field, and save the mapping.
2. In the Workflow Manager, configure the workflow or session to use a parameter file.
3. Set $ParamMyOverride to the SQL UPDATE statement below the appropriate heading in the parameter file.

We can also use a parameter file to override service and service process properties defined in the Administrator tool.

Example:

We can override the session log directory, $PMSessionLogDir. To do this, configure the workflow or session to use a parameter file and set $PMSessionLogDir to the new file path in the parameter file.

We can specify parameters and variables for the following PowerCenter objects:

Sources: You can use parameters and variables in input fields related to sources.

• Targets: You can use parameters and variables in input fields related to targets.
• Transformations: You can use parameters and variables in input fields related to transformations.
• Tasks: You can use parameters and variables in input fields related to tasks in the Workflow Manager.
• Sessions: You can use parameters and variables in input fields related to Session tasks.
• Workflows: You can use parameters and variables in input fields related to workflows.
• Connections: You can use parameters and variables in input fields related to connection objects. 
• Data profiling objects: You can use parameters and variables in input fields related to data profiling.

Some of the important session parameters:

·         $PMSessionLogFile is defines the name of the session log between session runs.

·         $InputFileName is defines a source file name and the parameter name using the appropriate prefix.

·         $LookupFileName is defines a lookup file name and the parameter name using the appropriate prefix.

·         $OutputFileNames is defines a target file name and the parameter name using the appropriate prefix.

·         $BadFileName is defines a reject file name and the parameter name using the appropriate prefix. 

·         $DBConnectionName is defines a relational database connection for a source, target, lookup, or stored procedure and Name the parameter using the appropriate prefix.

·         $ParamName is defines any other session property. For example, you can use this parameter to define a table owner name, table name prefix, FTP file or directory name, lookup cache file name prefix, or email address. You can use this parameter to define source, lookup, target, and reject file names, but not the session log file name or database connections and the parameter name using the appropriate prefix.

·         $PMFolderName will return the folder name.

·         $PMWorkflowName will return the workflow name.