Abstract: Kettle does not automatically replace variable references in parameter values. Here's a transformation that grabs all parameters of the containing job, substitutes and variable references inside the parameter values, and then overwrites the original parameters.
Authors: Roland Bouman
Kettle version: Kettle 3.2.0 and up
- substitute-variables-in-parameters.ktr - A transformation that grabs the parameters from the containing job and overwrites their original value with a variable substituted value. Simply include this transformation as the first job entry in your main job to support variable references in job parameter values. No further configuration required.
- test-substitute-variables-in-parameters.kjb - A sample job that illustrates how to use [substitute-variables-in-parameters.ktr|http://wiki.pentaho.com/download/attachments/20283718/substitute-variables-in-parameters.ktr] and demonstrates its behavior.
- test-dump-variables.ktr - a helper transformation used by the test job to prove the variable substitution works.
Kettle jobs and transformations offers support for named parameters(as of version 3.2.0). Named parameters form a special class of ordinary variables and are intended to clearly and explicitly define for which variables the caller should supply a value.
Kettle doesn't automatically substitute variable references in parameter values (nor in ordinary variable values). So, if you need to support variable references inside parameter values, you have to substitute the variables yourself.
As of Kettle version 4.01, the Calculator supports a calculation type called "variable substitution in string A" that is intended exactly for that purpose. I have tested this but unfortunately in 4.01 it doesn't seem to work, at least not for the built-in variable $
Putting these things together, we can create a transformation that reads the parameters from the job that contains the current transformation (using the built-in variables $
The result is a self-contained transformation that you can drop inside any job to support variable references in job parameters. Obviously, the transformation should appear in the job before any other contained job or transformation that needs to read the substituted parameter values.