Substitution parameters in SAP DS
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.
Nice document...! 🙂
Thanks, Subbarao ! 🙂
Very helpful - thanks.
Nice Document... Thanks
Good document ... thanks..
just saved my day! 😆
Substitution variables are actually job level constants (immutable), which pull their default values from the repository definition.
So they can be changed from job to job(executing concurrently) without affecting the entire repository.
Good<good document ..nicely explained 🙂
Can we use substitution parameter in File format root directory
Yes, we can. But in case, if you are to create a file format you need to use the actual path, once the file format is saved then you can replace the actual root path with Substitution parameter.
Very good and simple explanation of Substitution Parameter Configuration. Thank You!
Thanks, Shaheen Makandar!
Very nice explanation and straight to the point. Thank you.
Thanks, Former Member!
May I know the maximum length of the parameter value? Is it 64 characters as well. Many thanks.
The maximum length for most repository types is 256. MySQL is 64 and MS SQL server is 128.
Very helpful docs!!!
Thanks for the Info ... Nice explanation ...
Could you please tell me that how we can use substitution parameter in Initial and Delta Load.
(will be really helpful if you can share me the link to related information)
one remark, this works not with GBQ Connection how we find out. Here the GBQ Project is included into the table properties (invisible - only you make a compare to a central repository). Don't know why SAP handle this in another way.