What is substitution parameter?
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:
Global Variables | Substitution Parameters |
Defined at Job Level | Defined at Repository Level |
Can not be shared across Jobs | Available to all Jobs in a repository |
Data-Type specific | 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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
36 | |
8 | |
7 | |
5 | |
5 | |
4 | |
4 | |
4 | |
3 | |
3 |