Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

enter image description here

like image 909
Ritesh Avatar asked May 06 '14 09:05

Ritesh


2 Answers

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):

Transformation for setting the variables

In the Set Variables element configure the variables:

Configuring 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!

Configuring the DB connection

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

Job

like image 155
Marcus Rickert Avatar answered Nov 15 '22 14:11

Marcus Rickert


All you need is the XLS input and the Set Variables step. Define your variables as being valid in the Root job and you can use them in subsequent jobs, as long as they're called by the same root job, when defining the connection.

The "Copy rows to result" and "Get rows from result" are used to send information (rows of data) from one transformation to the next transformation or job in the same parent job. They're not used to send data between steps, that's what the hops are for.

like image 1
nsousa Avatar answered Nov 15 '22 12:11

nsousa