I'm a beginner in pentaho, I'm trying to pass parameters from jenkins for the transformation I'm working on,
Transformation details: Joining two tables from mssql and writing it in psql.
What I tried is I passed the parameters from jenkins using build with parameters option, but pentaho coudn't fetch the value from jenkins.
Execute shell step in my Jenkins job:
#!/bin/bash +x
PDI_HOME=/home/pentaho/pdi-ce-9.4.0.0-343/data-integration
cd "$PDI_HOME"
JOB_FILE="/home/pentaho/merge_table_mmsql_to_psql_job.kjb"
sh kitchen.sh \
-file="$JOB_FILE" \
-param:host_name_mssql="$HOST_NAME_MSSQL" \
-param:database_name_mssql="$DATABASE_NAME_MSSQL" \
-param:instance_name_mssql="$INSTANCE_NAME_MSSQL" \
-param:user_name_mssql="$USER_NAME_MSSQL" \
-param:user_pass_mssql="$USER_PASS_MSSQL" \
-param:host_name_psql="$HOST_NAME_PSQL" \
-param:database_name_psql="$DATABASE_NAME_PSQL" \
-param:user_name_psql="$USER_NAME_PSQL" \
-param:user_pass_psql="$USER_PASS_PSQL"
Pentaho connection screenshot: Database connection screenshot
Error I'm facing:
2024/02/26 12:57:20 - Kitchen - Start of run.
2024/02/26 12:57:24 - merge_table_mmsql_to_psql_job - Start of job execution
2024/02/26 12:57:24 - merge_table_mmsql_to_psql_job - Starting entry [Transformation]
2024/02/26 12:57:25 - Transformation - Using run configuration [Pentaho local]
2024/02/26 12:57:25 - merge_table_mssql_to_psql - Dispatching started for transformation [merge_table_mssql_to_psql]
2024/02/26 12:57:25 - table_output.0 - ERROR (version 9.4.0.0-343, build 0.0 from 2022-11-08 07.50.27 by buildguy) : An error occurred intialising this step:
2024/02/26 12:57:25 - table_output.0 - Error occurred while trying to connect to the database
2024/02/26 12:57:25 - table_output.0 -
2024/02/26 12:57:25 - table_output.0 - Connection failed. Verify all connection parameters and confirm that the appropriate driver is installed.
2024/02/26 12:57:25 - table_output.0 - The connection attempt failed.
2024/02/26 12:57:25 - table_input_mssql.0 - ERROR (version 9.4.0.0-343, build 0.0 from 2022-11-08 07.50.27 by buildguy) : An error occurred, processing will be stopped:
2024/02/26 12:57:25 - table_input_mssql.0 - Error occurred while trying to connect to the database
2024/02/26 12:57:25 - table_input_mssql.0 -
2024/02/26 12:57:25 - table_input_mssql.0 - Connection failed. Verify all connection parameters and confirm that the appropriate driver is installed.
2024/02/26 12:57:25 - table_input_mssql.0 - Unknown server host name '${host_name_mssql}'.
2024/02/26 12:57:25 - table_output.0 - ERROR (version 9.4.0.0-343, build 0.0 from 2022-11-08 07.50.27 by buildguy) : Error initializing step [table_output]
2024/02/26 12:57:25 - table_input_mssql.0 - ERROR (version 9.4.0.0-343, build 0.0 from 2022-11-08 07.50.27 by buildguy) : Error initializing step [table_input_mssql]
2024/02/26 12:57:25 - merge_table_mssql_to_psql - ERROR (version 9.4.0.0-343, build 0.0 from 2022-11-08 07.50.27 by buildguy) : Step [table_input_mssql.0] failed to initialize!
2024/02/26 12:57:25 - merge_table_mssql_to_psql - ERROR (version 9.4.0.0-343, build 0.0 from 2022-11-08 07.50.27 by buildguy) : Step [table_output.0] failed to initialize!
2024/02/26 12:57:25 - table_input_mssql.0 - Finished reading query, closing connection
2024/02/26 12:57:25 - table_output.0 - ERROR (version 9.4.0.0-343, build 0.0 from 2022-11-08 07.50.27 by buildguy) : Unexpected error rolling back the database connection.
2024/02/26 12:57:25 - table_output.0 - ERROR (version 9.4.0.0-343, build 0.0 from 2022-11-08 07.50.27 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
2024/02/26 12:57:25 - table_output.0 - Unable to get database metadata from this database connection
2024/02/26 12:57:25 - table_output.0 -
2024/02/26 12:57:25 - table_output.0 - Error connecting to database [${DATABASE_NAME_PSQL}]
2024/02/26 12:57:25 - table_output.0 -
2024/02/26 12:57:25 - table_output.0 -
2024/02/26 12:57:25 - table_output.0 - at org.pentaho.di.core.database.Database.getDatabaseMetaData(Database.java:3121)
2024/02/26 12:57:25 - table_output.0 - at org.pentaho.di.core.database.Database.rollback(Database.java:912)
2024/02/26 12:57:25 - table_output.0 - at org.pentaho.di.core.database.Database.rollback(Database.java:904)
2024/02/26 12:57:25 - table_output.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:625)
2024/02/26 12:57:25 - table_output.0 - at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:1272)
2024/02/26 12:57:25 - table_output.0 - at org.pentaho.di.trans.Trans.execute(Trans.java:763)
2024/02/26 12:57:25 - table_output.0 - at org.pentaho.di.job.entries.trans.JobEntryTrans.execute(JobEntryTrans.java:1189)
2024/02/26 12:57:25 - table_output.0 - at org.pentaho.di.job.Job.execute(Job.java:707)
2024/02/26 12:57:25 - table_output.0 - at org.pentaho.di.job.Job.execute(Job.java:848)
2024/02/26 12:57:25 - table_output.0 - at org.pentaho.di.job.Job.execute(Job.java:517)
2024/02/26 12:57:25 - table_output.0 - at org.pentaho.di.job.Job.run(Job.java:395)
2024/02/26 12:57:25 - table_output.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
2024/02/26 12:57:25 - table_output.0 - Error connecting to database [${DATABASE_NAME_PSQL}]
2024/02/26 12:57:25 - table_output.0 -
2024/02/26 12:57:25 - table_output.0 - at org.pentaho.di.core.database.Database.getDatabaseMetaData(Database.java:3115)
2024/02/26 12:57:25 - table_output.0 - ... 10 more
2024/02/26 12:57:25 - Transformation - ERROR (version 9.4.0.0-343, build 0.0 from 2022-11-08 07.50.27 by buildguy) : Unable to prepare for execution of the transformation
2024/02/26 12:57:25 - Transformation - ERROR (version 9.4.0.0-343, build 0.0 from 2022-11-08 07.50.27 by buildguy) : org.pentaho.di.core.exception.KettleException:
2024/02/26 12:57:25 - Transformation - We failed to initialize at least one step. Execution can not begin!
2024/02/26 12:57:25 - Transformation -
2024/02/26 12:57:25 - Transformation -
2024/02/26 12:57:25 - Transformation - at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:1301)
2024/02/26 12:57:25 - Transformation - at org.pentaho.di.trans.Trans.execute(Trans.java:763)
2024/02/26 12:57:25 - Transformation - at org.pentaho.di.job.entries.trans.JobEntryTrans.execute(JobEntryTrans.java:1189)
2024/02/26 12:57:25 - Transformation - at org.pentaho.di.job.Job.execute(Job.java:707)
2024/02/26 12:57:25 - Transformation - at org.pentaho.di.job.Job.execute(Job.java:848)
2024/02/26 12:57:25 - Transformation - at org.pentaho.di.job.Job.execute(Job.java:517)
2024/02/26 12:57:25 - Transformation - at org.pentaho.di.job.Job.run(Job.java:395)
2024/02/26 12:57:25 - merge_table_mmsql_to_psql_job - Finished job entry [Transformation] (result=[false])
2024/02/26 12:57:25 - merge_table_mmsql_to_psql_job - Job execution finished
2024/02/26 12:57:25 - Kitchen - Finished!
2024/02/26 12:57:25 - Kitchen - ERROR (version 9.4.0.0-343, build 0.0 from 2022-11-08 07.50.27 by buildguy) : Finished with errors
2024/02/26 12:57:25 - Kitchen - Start=2024/02/26 12:57:24.690, Stop=2024/02/26 12:57:25.328
2024/02/26 12:57:25 - Kitchen - Processing ended after 0 seconds.
Build step 'Execute shell' marked build as failure
Finished: FAILURE
Still not really sure what you are trying to do here... Missing quite a bit of information for a full overview. I asume you have your database connection in pentaho setup in such a way that the parameters (database, user, port etc) are filled in as parameters (host=${HOST_NAME_MSSQL}, etc).
Then you should have these variable defined in your highest job (rootjob) as standard parameters with an empty default. (a default is allowed, but better to keep empty to not accidently connect to the wrong system on failure).
This should be fine because as you stated before hardcoding the parameters in root will give you a good connection.
After that the only possible issue is how the parameters are passed into the job. You might be able to check this by adding a get variables step, retrieving the db values, and sending the result to a log step. Then you can see what goes wrong and correct it.
For a more detailed answer we would need a lot more info. Like: