What is substitution parameter?
- Substitution parameters are used to store constant values and defined at repository level.
- Substitution parameters are accessible to all jobs in a repository.
- Substitution parameters are useful when you want to export and run a job containing constant values in a specific environment.
Scenario to use Substitution Parameters:
For instance, if you create multiple jobs in a repository and those references a directory on your local computer to read the source files. Instead of creating global variables in each job to store this path you can use a substitution parameter instead. You can easily assign a value for the original, constant value in order to run the job in the new environment. After creating a substitution parameter value for the directory in your environment, you can run the job in a different environment and all the objects that reference the original directory will automatically use the value. This means that you only need to change the constant value (the original directory name) in one place (the substitution parameter) and its value will automatically propagate to all objects in the job when it runs in the new environment.
Key difference between substitution parameters and global variables:
- You would use a global variable when you do not know the value prior to execution and it needs to be calculated in the job.
- You would use a substitution parameter for constants that do not change during execution. By using a substitution parameter means you do not need to define a global variable in each job to parameterize a constant value.
Defined at Job Level
Defined at Repository Level
Can not be shared across Jobs
Available to all Jobs in a repository
No data type (all strings)
Value can change during job execution
Fixed value set prior to execution of Job (constants)
How to define the Substitution Parameters?
Open the Substitution Parameter Editor from the Designer by selecting
Tools > Substitution Parameter Configurations….
• You can either add another substitution parameter in existing configuration or you may add a new configuration by clicking the Create New Substitution Parameter Configuration icon in the toolbar.
• The name prefix is two dollar signs $$ (global variables are prefixed with one dollar sign). When
adding new substitution parameters in the Substitution Parameter Editor, the editor automatically
adds the prefix.
• The maximum length of a name is 64 characters.
In the following example, the substitution parameter $$SourceFilesPath has the value D:/Data/Staging in the configuration named Dev_Subst_Param_Conf and the value C:/data/staging in the Quality_Subst_Param_Conf configuration.
This substitution parameter can be used in more than one Jobs in a repository. You can use substitution parameters in all places where global variables are supported like Query transform WHERE clauses, Scripts, Mappings, SQL transform, Flat-file options, Address cleanse transform options etc. Below script will print the source files path what is defined above.
Print (‘Source Files Path: [$$SourceFilesPath]’);
Associating a substitution parameter configuration with a system configuration:
A system configuration groups together a set of datastore configurations and a substitution parameter configuration. For example, you might create one system configuration for your DEV environment and a different system configuration for Quality Environment. Depending on your environment, both system configurations might point to the same substitution parameter configuration or each system configuration might require a different substitution parameter configuration. In below example, we are using different substitution parameter for DEV and Quality Systems.
To associate a substitution parameter configuration with a new or existing system configuration:
In the Designer, open the System Configuration Editor by selecting
Tools > System Configurations
You may refer this blog to create the system configuration.
The following example shows two system configurations, DEV and Quality. In this case, there are substitution parameter configurations for each environment. Each substitution parameter configuration defines where the data source files are located. Select the appropriate substitution parameter configuration and datastore configurations for each system configuration.
At job execution time, you can set the system configuration and the job will execute with the values for the associated substitution parameter configuration.
Exporting and importing substitution parameters:
Substitution parameters are stored in a local repository along with their configured values. The DS does not include substitution parameters as part of a regular export. Therefore, you need to export substitution parameters and configurations to other repositories by exporting them to a file and then importing the file to another repository.
Exporting substitution parameters
- Right-click in the local object library and select Repository > Export Substitution Parameter
- Select the check box in the Export column for the substitution parameter configurations to export.
- Save the file.
The software saves it as a text file with an .atl extension.
Importing substitution parameters
The substitution parameters must have first been exported to an ATL file.
- In the Designer, right-click in the object library and select Repository > Import from file.
- Browse to the file to import.
- Click OK.