Pentaho

 View Only

 Problem with kettle.properties - variables in shared connection

Heiko Markwart's profile image
Heiko Markwart posted 04-20-2023 06:05

Hi! 

I have set up a new system with Pentaho 9.3. The configuration uses a shared KETTLE_HOME directory (same as the ".\data-integration"-folder), where (in the .kettle-subfolder) the files kettle.properties and shared.xml is stored. 

In the database connections in shared.xml, the fields "host name", "instance", "user name" and "password" are variables which are defined in kettle.properties. 

Now, when I run a connection test on one of the database connections or start a job or transformations which uses them, they fail, because the variables are not set. When I try to press CTRL+SPACE in one of the fields of the connection, the variables don't exist.

When I now execute "Edit" > "Edit the kettle.properties file", and just confirm with "OK", the variables got populated and everything works fine.

I tried to several approaches to set KETTLE_HOME, KETTLE_PROPERTIES or KETTLE_FILE in my Spoon.bat, but it didn't change anything. And the real problem is, that this not only occurs in Spoon, but also when the jobs are executed in Carte, where I have no manual workaround available. Please help.

Petr Prochazka's profile image
Petr Prochazka posted 04-27-2023 14:29

Hi Heiko,
are you run job from repository or run as single loaded file?

Heiko Markwart's profile image
Heiko Markwart posted 04-28-2023 01:51

Hi Petr, 
it is PDI CE - therefore run from a file.

Heiko Markwart's profile image
Heiko Markwart posted 04-28-2023 02:38

I found a similar post at SmartOverflow: https://stackoverflow.com/questions/75177947/pentaho-data-integration-community-edition-9-4-shared-objects-file-feature-of

It seems that this is the same problem.  I can confirm that my solution worked in PDI CE 9.2, the same does not work in 9.3. I think that this was a major release change, as the Java support changed from java 8 to Java 11. 

Petr Prochazka's profile image
Petr Prochazka posted 04-28-2023 06:24

Have you tried run job directly from command line via kitchen.bat?

Could you provide DB connection in shared.xml?

Heiko Markwart's profile image
Heiko Markwart posted 05-02-2023 03:49

Hi Petr,

I did not try Kitchen, but it did not work with Carte. Is it possible that it works with Kitchen?

And yes, my DBConnections are in shared.xml, which is stored in the .kettle - subdirectory of my application root directory D:\application\pdi\data-integration; KETTLE_HOME points to that directory. The difference is, now I am forced to hard code my server in the shared.xml file, while with 9.2, it was possible to use variable from kettle.properties. My application is a standard ETL solution which is rolled out to customer environments which use individual configurations of the MS SQL Server, I can't predefine server, instance, login name and pwd. 


Heiko Markwart's profile image
Heiko Markwart posted 06-14-2023 10:28

After a lot of trial & error, I found an approach that works. I added one line of code to Spoon.bat (last line in the following sequence, in red color; please note the dot "." after the "="); this triggers PDI effectively to use the .kettle directory in the program installation folder and not in the user's folder.

:: **************************************************
:: ** Kettle home                                  **
:: **************************************************
if "%KETTLE_DIR%"=="" set KETTLE_DIR=%~dp0
if %KETTLE_DIR:~-1%==\ set KETTLE_DIR=%KETTLE_DIR:~0,-1%
 
cd %KETTLE_DIR%
set KETTLE_HOME=.

Related Content