Pass value from job to transformation in Pentaho
I have the following transformation in Pentaho PDI (note the question mark in the SQL statement):
The transformation is called from a job. What I need is to get the value from the user when the job is run and pass it to the transformation so the question mark is replaced.
My problem is that there are parameters, arguments and variables, and I don't know which one to use. How to make this work?
What karan means is that your sql should look like delete from REFERENCE_DATA where rtepdate = ${you_name_it}, and check the box Variable substitution. The you_name_it parameter must be declared in the transformation option (click anywhere in the spoon panel, Option/Parameters), with or without a default value.
When running the transformation, you are prompted with a panel where you can set the value of the parameters, including you_name_it.
Parameters pass from job to transformation transparently, so you can declare you_name_it as a parameter of the job. Then when the user run the job, it will be prompted to give values to a list of parameters, including you_name_it.
An other way to achieve the same result, is to use arguments. The question marks will be replaced by the fields specified in the Parameters list box, in the same order. Of course the field you use must be defined in a previous step. In your case, a Get variable step, which reads the variable defined in the calling job, and put them in a row.
Note that, there is a ready made Delete step to delete records from a database. Specify the table name (which can be a parameter: just Crtl+Space in the box), the table column and the condition. The condition will come from a previous step defined in a Get parameter like in the argument method.