Jenkins自由风格任务向Pentaho传递数据库连接参数失败求助

我是Pentaho新手，正在开发将MSSQL两张表合并后写入PostgreSQL的转换任务。尝试通过Jenkins带参数构建传递数据库连接参数，但Pentaho未解析这些参数，直接使用占位符导致数据库连接失败。

Jenkins执行脚本 #

#!/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" 

错误信息 #

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

解决方案 #

1. 核对Pentaho参数配置 #

• 打开Spoon编辑数据库连接，确认主机、库名等字段使用${参数名}格式，例如${host_name_mssql}
• 保证参数名大小写完全匹配：Jenkins传递的是host_name_mssql，Pentaho中不能写成大写形式

2. 验证Jenkins参数传递 #

• 在Jenkins任务的「参数化构建过程」中，确认已添加对应字符串参数（如HOST_NAME_MSSQL），且名称和脚本中引用的一致
• 在Execute Shell步骤添加调试命令，查看参数是否正确获取：

echo "MSSQL Host: $HOST_NAME_MSSQL"
echo "PSQL Database: $DATABASE_NAME_PSQL"

执行构建后查看控制台输出，确认参数已传入脚本环境

3. 配置Job到Transformation的参数传递 #

如果Job调用了Transformation，需手动传递参数：

1. 打开Job中的「Transformation」任务项
2. 切换到「Parameters」标签页
3. 添加所有需要传递的参数，设置「Parameter name」为Transformation使用的参数名，「Value」为Job接收的参数（如${host_name_mssql}）

4. 启用数据库连接的变量替换 #

• 在Spoon中编辑数据库连接，点击「Options」按钮
• 添加KETTLE_VARIABLES参数，值设为Y
• 保存连接后重新运行Job

5. 确认Kitchen命令格式 #

检查kitchen.sh参数格式是否正确：

• 格式应为-param:参数名=参数值，冒号后无空格（当前脚本格式正确）
• 若参数值含特殊字符，需用引号包裹（当前脚本已处理）

内容的提问来源于stack exchange，提问作者Aravind S