Substituting variable references in Job Parameter values

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

License: LGPL

Kettle version: Kettle 3.2.0 and up

Attachments

Background

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 $

 which I used in my test. In the latest stable version, Kettle 4.10 it does work as advertised, I would recommend using this method if you're a user of Kettle 4.10 (or later). In Kettle 3.2.0 (and up) you can use the javascript step to work with variables. With the built-in function getVariable(name, type) you can get the value of a variable, and with the built-in function setVariable(name, value, type) you can set the value of a variable.

The getVariable() built-in javascript function, plus some standard regular expression magic allow you to create a generic javascript function to replace all variables in a give value passed in by the incoming stream. With the Get Data from XML step, we can analyze any kettle job or transformation file and extract the names of the parameters defined by that job or transformation.

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 ${Internal.Job.Filename.Directory} and ${Internal.Job.Filename.Name}). By feeding the parameter names into the javascript step, we can get their runtime value, and substitute any variable references inside the parameter values. Finally, also in he javascript step, we can overwrite the original parameter value with the variable substituted one using the built-in setVariable() function.

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.

See also

http://rpbouman.blogspot.com/2010/12/substituting-variables-in-kettle.html