Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Environment Variables for Database Connection, Sourced from Kettle Repository Table

  1. #1
    Join Date
    Jun 2007
    Posts
    12

    Cool Environment Variables for Database Connection, Sourced from Kettle Repository Table

    I have a table in the kettle repository called 'system_parameters'. In the spoon.bat, I specify the variables to connect to the kettle repository.

    The first job that I have in any process is to load the environment variables from the system_parameters table in the kettle repository.

    I am able to use parameters from this table for the most part without any issues.

    In this table however, I would also like to specify the connection properties to connect to the source and target databases. The host, the username, the port, the password, the database....

    This doesn't appear to be working as when I try to 'test' the connections in the UI after entering/specifying the variable names I have created in a the 'load variables' step, it errors. It is trying to build a connection string without an empty server name and an empty port number, etc.

    Am I only able to specify database connection variables in the spoon.bat? Or is it possible to do what I am doing, and source them from a database table?

    Thanks!

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    variables are a runtime thing if you set them via set variable step. If you want to test it, set the variables in kettle.properties for testing purposes.

    Also with set variables you can set the value, not the name I think. So you would have to a javascript step to set key/value variable names, in 3.0 there's a new interface for it in the javascript step, in 2.5 you have open up some internals from Kettle (it's been discussed on the developers mailing list a week or so ago).

    And lastly you can't use variables in the same transformation as where you set... you have to have a combination of a job execution multiple transformations where a first transformations sets the variables which you then can you use in later transformations.

    Regards,
    Sven

  3. #3
    Join Date
    Jun 2007
    Posts
    12

    Red face

    Thanks for the advice. To be sure that I understand you correctly, I'm going to explain my situation a bit further. From what I understood/read of your answer, it doesn't seem like what I'm trying to do wouldn't work.


    I have a master job that runs, and the first thing it does is executes a transform that sets the environment variables using the "set values" step using a "table input". By doing this, I take the column names as the variable names.

    After this completes, the master job then continues to execute other Jobs. These jobs can contain other jobs that contain transforms. In these transforms, there are connections to a source db and a target db. These connections are what I would like to have set in the first step in the first transform.


    If I move that initial transform to a job that contains this transform, would that help with the variable scope? I have set them to 'valid in the java virtual machine' and all of them work, except for those that are to be consumed by the db connection parameters.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.