Pass DB Connection parameters to a Kettle a.k.a PDI table Input step dynamically from Excel

Question

I have a requirement such that whenever i run my Kettle job, the database connection parameters must be taken dynamically from an excel source on each run.

Say i have an excel with column names : HostName, Username, Database, Password.

i want to pass these connection parameters to my table input step dynamically whenever the job runs.

This is what i was trying to do.

Answer 1

You can achieve this by

• reading the DB connection parameters from a source (e.g. Excel or in my example a CSV file)
• storing the parameters in variables
• using the variables in your connection setting.

Proceed as follows

• Create another transformation for setting the variables (you cannot do this in the same transformation that uses it):

In the Set Variables element configure the variables:

• In the element reading/writing your data create a new connection and set the connection parameters using ${variable_name}. Note that you have to blindly write ${password} into the appropriate field. Also note that this may be a security issue because the value may show up as plain text in log files!

• In your job call the variable transformation first and then the functional part: