Search code examples
pentahokettlespoon

How to pass variables between steps inside a transformation in Pentaho

I want to design a ETL job as attached.enter image description here

Step 1 will set two variables Variable_1 and Variable_2. I am using Generate Rows step for doing this where I define both variables in Fields.

Step 2 will read a date from database through a SQL query. I am using Table input to achieve this. And the sql is like select Variable_3 from table1 which is a single value. Preview here shows that the value is read successfully from database.

I want step 3 as Modified Java Script Value where I want to use these 3 variables and do some calculation to derive the output Variable_4 which I then want to pass on to future transformation for use there.

I am new to Pentaho and have the following questions:

1) When I connect Step 2 (database step) to Step 3 (Javascript step), I get a warning dialog box:

We detected rows with varying number of fields, this is not allowed in a transformation. The first row contained 2 fields, another one contained 1

What does this warning mean? What am I doing wrong? How can I pass 2 variables from 1st step and 1 from 2nd step to my javascript step?

2) How do I set the calculated Variable_4 in my Javascript step for further use in other transformation? Do I define a Parameter on Transformation 1 named Variable_4 and set it from javascript? If yes, how?

Solution

  • I resolved this by removing the first step (Generate Rows creating two variables) and put in a Add Constants step after Table View step to add Variable 1 and Variable 2 along side Variable 3 passed to it from the Table View step.

    Finally, I have all 3 of my variables available in my Javascript step.

    I am still not been able to figure out Question 2) though.