Access Keys:
Skip to content (Access Key - 0)

Introduction

Named parameters are a system that allows you to parameterize your transformations and jobs.  On top of the variables system that was already in place prior to the introduction in version 3.2, named parameters offer the setting of a description and a default value.  That allows you in turn to list the required parameters for a job or transformation.

How it works

You can go to the settings dialog of your transformation or job and there you will find the "Parameters" tab...


 
Once these parameters are set they are used at runtime.  If a value is set, that is used.  If no value is set for a parameter, the default is used.

Passing values

Spoon development

The execution dialog of transformations and jobs allow you to set a value for each named parameter that is defined...

At the command line

In both Pan and Kitchen you can list the defined parameters:

user@host:$ sh pan.sh -file:/tmp/foo.ktr -listparam
Parameter: MASTER_HOST=, default=localhost : The master slave server hostname to connect to
Parameter: MASTER_PORT=, default=8080 : The master slave server HTTP control port

You can also define parameters during execution like this:

user@host:$ sh pan.sh -file:/tmp/foo.ktr -param:MASTER_HOST=192.168.1.3 -param:MASTER_PORT=8181

Windows requires you to use quotes around the parameter otherwise the equals sign is treated as a space by the command interpreter:
c:\> pan.sh -file:/tmp/foo.ktr "-param:MASTER_HOST=192.168.1.3" "-param:MASTER_PORT=8181"




In job entries

In both the Job and Transformation job entries in a job you can define how parameters are set and/or passed:

As you can see from the screenshot all parameters defined in the parent job are passed down by default.  You can also selectively pass parameters down by specifying them in the grid.

You can either set the value of a parameter by giving it a value OR by looking up the value in a Result row from a previous transformation.   Simply specify a column name in that case.

In Mapping (execute sub-transformation) Step

You can pass named parameter values to sub-transformations in the 'Parameters' tab of the 'Mapping (execute sub-transformations)' step:
Atention:

As of PDI 4.2.x (?), the named parameter must be defined in the sub-transformation, otherwise the value set here will be ignored.


  1. Mar 03, 2010

    Jan Schreier says:

    I find it worth mentioning that parameters passed via the command line MUST BE d...

    I find it worth mentioning that parameters passed via the command line MUST BE defined in the job to work. If you know this is the case, you can read it from the current description if you don't it is easily overlooked.

  2. Jan 05, 2011

    Dor Porat says:

    I must say that I have not found one place that explains how are you suppose to ...

    I must say that I have not found one place that explains how are you suppose to get the value from the named parameter within the job/transformation, this article explains only how to set a named parameter value.

    I'm not sure on whether this is the place to elaborate on the issue, but I find no other candidate.

  3. Jan 06, 2011

    Jens Lærkedal says:

    In answer to Dor Porat: Open job PROPERTIES. Open the PARAMETERS Tab. Define you...

    In answer to Dor Porat: Open job PROPERTIES. Open the PARAMETERS Tab. Define your parameter here. It will then be available for insertion anywhere in the job, where you can use variables. F. i. you can set a variable and in the "value" field press ctrl-space, then insert your parameter from the drop-down list.

  4. Jan 17, 2011

    Steve Donie says:

    Jens -I am trying to use your suggestion, but I don't quite understand. The ...

    Jens -I am trying to use your suggestion, but I don't quite understand. The 'set variable' step has columns for 'field name' and 'variable name', but not 'value'. Ctrl+Space in either of those columns does nothing for me.

    (edit) - OK - figured this out. I needed to use the 'get variable' step rather than 'set variable'

  5. Jan 18, 2011

    Neal Cowles says:

    To use these parameters to make a connection dynamic, simply configure under "se...

    To use these parameters to make a connection dynamic, simply configure under "settings" the connection host, user, pass, etc. values as the variable (dollar sign, open curly brace, variable name, close curly brace ).

  6. Feb 08, 2011

    Jens Lærkedal says:

    I was focused on the use of parameters in JOBS, where it is fairly obvious. As S...

    I was focused on the use of parameters in JOBS, where it is fairly obvious. As Steve points out, in order to use parameters in a TRANSFORMATION, you will need to apply "Get Variables", in order to "transform" the parameter values to "rows". The big advantage of parameters in a JOB is, that you can define them on a command line, from which you start Data-integration, and they are as easily accessible as variables from within the job.

  7. Jun 23, 2011

    Howard Webb says:

    My pre-existing non-Kettle application (PHP) uses environment variables to confi...

    My pre-existing non-Kettle application (PHP) uses environment variables to configure all manner of environment-specific stuff which the application references dynamically to ensure that it readily deploys in parallel configurations without re-coding.

    I'd like to use named parameters to acheive the same level of environmental abstraction in Kettle and I can see that this is possible in Pan and Kitchen by using -param on their command lines via a shell script wrapper to map environment variable to named parameter. 

    However, I cannot see that there is an equivalent facility for Carte which would be my preferred invocation mechanism in production. Any suggestions?

  8. Jul 27, 2011

    Darren Hartford says:

    Java API call version:    trans.addParameterDefinition("batchXmlFile",...

    Java API call version:    trans.addParameterDefinition("batchXmlFile", batchXmlFile, null);
    trans.addParameterDefinition("MASTER_HOST", "192.168.1.3", null);

    trans.addParameterDefinition("MASTER_PORT", "8181", null);

  9. Apr 03, 2013

    Katerina Mpagouli says:

    In Mapping (execute sub-transformation) Step How could I use a field value as t...

    In Mapping (execute sub-transformation) Step

    How could I use a field value as the value of the parameter I pass here (instead of a variable)?

  10. Jun 18, 2013

    David Speck says:

    I am fairly new to using Kettle and am creating a transformation in Spoon that l...

    I am fairly new to using Kettle and am creating a transformation in Spoon that logs into SalesForce multiple times to get data.  I have setup parameters for the SalesForce URL, user id, and password.  Is there a way to obfuscate the text of a parameter so I can type it in when running the transformation, but someone couldn't read the text on my screen?  

  11. Aug 22, 2013

    lourdhu rajesh kumar says:

    Hi all, I'm having the following situation, My transformation having below steps...

    Hi all, I'm having the following situation,
    My transformation having below steps, and i need to use a named parameter in my table input to get a dynamic SQL.

    GetSystemInfo > TablesInput --> CSV Ouput

    I have tried GetVariables Step, but my table input showing following error: Unknown column '$' in 'where clause'.
    The Named Parameter i'm having is: POLICY_NUMBER
    My GetVariables step:     
    don't know how to use the Named Parameter in my SQL.

    Thanks in advance

  12. Aug 22, 2013

    Jens Lærkedal says:

    Dear Lourhu Seems the variable POLICY_NUMBER is not known in the transformation...

    Dear Lourhu

    Seems the variable POLICY_NUMBER is not known in the transformation. Did you indicate it as a parameter both in the transformation properties and at the box, where you call the transformation? If feasible, I would suggest to define the variable in the calling job instead.

    Kind regards Jens Lærkedal

  13. Aug 22, 2013

    lourdhu rajesh kumar says:

    Hi jens, Thanks for ur quick rly. I have indicated POLICY_NUMBER in my transfor...

    Hi jens, Thanks for ur quick rly.

    I have indicated POLICY_NUMBER in my transformation properties, and in the GetVariables step.

    Also i'm trying to call the transformation using my pentaho report which is having criteria like POLICY_NUMBER...

    so only i'm looking for getting the Variables inside a transformation.

    I doubt whether the syntax i'm using in the SQL is correct, does it picking the correct parameter value.

    Error i'm getting now is:

    2013/08/22 12:11:15 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
    2013/08/22 12:11:15 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : offending row : [POLICY_NUMBER String(3)], [FROM_DATE String]
    2013/08/22 12:11:15 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
    2013/08/22 12:11:15 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Error setting value #2 [FROM_DATE String] on prepared statement (String)
    2013/08/22 12:11:15 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
    2013/08/22 12:11:15 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Parameter index out of range (2 > number of parameters, which is 1).

    please correct If i'm on the wrong path.

  14. Aug 22, 2013

    Jens Lærkedal says:

    Hi Lourdhu There is nothing wrong with using variables insides SQL expressions,...

    Hi Lourdhu

    There is nothing wrong with using variables insides SQL expressions, like this example from one of my one systems:

    SELECT ... FROM ...

    WHERE  Udtrk.PdUnitType = 'Udtræk til tidende'
    AND Udtrk.PdunitNavn like '$

    Unknown macro: {BNR}

    ;%$

    Unknown macro: {YEAR}

    -%Dansk Brugsmodeltidende"'
     AND    Udtrk.PdUnitType = R1.UnderPdUnitType
     AND    Udtrk.PdUnitId = R1.UnderPdUnitId

    You need to check, if the variable is actually known at this place in the transformation. Try writing the variable in the log and inspect. Then check any error messages from the database call.

    Best regards Jens Lærkedal

  15. Aug 22, 2013

    Jens Lærkedal says:

    Ups, there is some interpretation of the variable names in my answer, sorry. It...

    Ups, there is some interpretation of the variable names in my answer, sorry.

    It should have been just plainly 

    AND Udtr.PdunitNavn like '<Dollarsign><CurlyBracket>BNR<EndCurlyBracket>;%Dollarsign><CurlyBracket>YEAR<EndCurlyBracket>-%Dansk Brugsmodeltidende'

  16. Aug 22, 2013

    lourdhu rajesh kumar says:

    Tried write to Log, before the get system info and got my Policy number but whe...

    Tried write to Log, before the get system info and got my Policy number

    but when i tried write to log after get system info, both Argument and parameter values are missing.

    and still got the same error in SQL

    2013/08/22 12:27:34 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Unexpected error
    2013/08/22 12:27:34 - Get Variables.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2013/08/22 12:27:34 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2013/08/22 12:27:34 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Unable to find rowset to read from, perhaps step [Criteria For Policy Details Import] doesn't exist. (or perhaps you are trying a preview?)
    2013/08/22 12:27:34 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
    2013/08/22 12:27:34 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : 	at org.pentaho.di.trans.steps.tableinput.TableInput.readStartDate(TableInput.java:90)
    2013/08/22 12:27:34 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : 	at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:122)
    2013/08/22 12:27:34 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : 	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    2013/08/22 12:27:34 - Policy Details Import.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : 	at java.lang.Thread.run(Thread.java:619)
  17. Aug 22, 2013

    Jens Lærkedal says:

    Dear Lourdhu Looks like You are not getting the variable value passed to the tr...

    Dear Lourdhu

    Looks like You are not getting the variable value passed to the transformation. I cannot see why. Did you try to define the variable in the calling job instead of passing it as a parameter to the transformation?

    Kind regards Jens Lærkedal

  18. Aug 22, 2013

    lourdhu rajesh kumar says:

    Hi Jens, Problem solved... :) I have removed the Get Variables Step from my tr...

    Hi Jens,

    Problem solved... :)

    I have removed the Get Variables Step from my transformation, and directly referred the Variables in my SQL and it worked..

    The SQL directly replace the Values for the Named Parameters within transformation.

  19. Aug 23, 2013

    lourdhu rajesh kumar says:

    Hi, I am getting following error when i try to call a kettle transformation usi...

    Hi,

    I am getting following error when i try to call a kettle transformation using a report.

    I have added my ktr as a data source in that report, everything working fine when i click preview.

    But after publishing the resultset is empty in my browser.

    INFO  23-08 13:16:41,710 - Policy Details Import - Finished reading query, closing connection.
    INFO  23-08 13:16:41,711 - Policy Details Import - Finished processing (I=2, O=0, R=1, W=0, U=0, E=1)
    INFO  23-08 13:16:41,711 - PolicyImport - PolicyImport
    INFO  23-08 13:16:41,711 - PolicyImport - PolicyImport
    INFO  23-08 13:21:08,272 - PolicyImport - Dispatching started for transformation [PolicyImport]
    INFO  23-08 13:21:08,272 - PolicyImport - This transformation can be replayed with replay date: 2013/08/23 13:21:08
    INFO  23-08 13:21:08,292 - Criteria For Policy Details Import - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    ERROR 23-08 13:21:08,293 - Policy Details Import - Unexpected error :
    ERROR 23-08 13:21:08,293 - Policy Details Import - java.lang.IndexOutOfBoundsException: Index: 41, Size: 41
    java.util.ArrayList.RangeCheck(ArrayList.java:547)
    java.util.ArrayList.get(ArrayList.java:322)
    org.pentaho.di.core.row.RowMeta.getValueMeta(RowMeta.java:156)
    org.pentaho.reporting.engine.classic.extensions.datasources.kettle.AbstractKettleTransformationProducer$TableProducer.rowWrittenEvent(AbstractKettleTransformationProducer.java:129)
    org.pentaho.di.trans.step.BaseStep.putRow(BaseStep.java:981)
    org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:191)
    org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    java.lang.Thread.run(Thread.java:619)
    
    INFO  23-08 13:21:08,297 - Policy Details Import - Finished reading query, closing connection.
    INFO  23-08 13:21:08,298 - Policy Details Import - Finished processing (I=2, O=0, R=1, W=0, U=0, E=1)
    INFO  23-08 13:21:08,298 - PolicyImport - PolicyImport
    INFO  23-08 13:21:08,298 - PolicyImport - PolicyImport
    INFO  23-08 13:21:08,459 - PolicyImport - Dispatching started for transformation [PolicyImport]
    INFO  23-08 13:21:08,459 - PolicyImport - This transformation can be replayed with replay date: 2013/08/23 13:21:08
    INFO  23-08 13:21:08,479 - Criteria For Policy Details Import - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    ERROR 23-08 13:21:08,479 - Policy Details Import - Unexpected error :
    ERROR 23-08 13:21:08,479 - Policy Details Import - java.lang.IndexOutOfBoundsException: Index: 41, Size: 41
    java.util.ArrayList.RangeCheck(ArrayList.java:547)
    java.util.ArrayList.get(ArrayList.java:322)
    org.pentaho.di.core.row.RowMeta.getValueMeta(RowMeta.java:156)
    org.pentaho.reporting.engine.classic.extensions.datasources.kettle.AbstractKettleTransformationProducer$TableProducer.rowWrittenEvent(AbstractKettleTransformationProducer.java:129)
    org.pentaho.di.trans.step.BaseStep.putRow(BaseStep.java:981)
    org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:191)
    org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    java.lang.Thread.run(Thread.java:619)
  20. Feb 12, 2014

    Garry Boyce says:

    If you want to parameterize a password, with a default but you want the default ...

    If you want to parameterize a password, with a default but you want the default value to be password hidden as in a regular password field is there a way to do this?

  21. Apr 03

    Ian Fore says:

    I am also interested in whether a the text field used in the Spoon interface to ...

    I am also interested in whether a the text field used in the Spoon interface to enter parameters (or environment variables) can be masked when it is used for a sensitive parameter like a password. Spoon masks passwords in the database connection dialog - it would be good to have the same option in any dialog for parameter entry. Is that possible? Thanks.

This documentation is maintained by the Pentaho community, and members are encouraged to create new pages in the appropriate spaces, or edit existing pages that need to be corrected or updated.

Please do not leave comments on Wiki pages asking for help. They will be deleted. Use the forums instead.

Adaptavist Theme Builder (4.2.0) Powered by Atlassian Confluence 3.3.3, the Enterprise Wiki