Search code examples
htmlsqldatabasepentahokettle

PDI parameter in select statement

I want to ask a question about Pentaho. I'm using Pentaho data Integration (Kettle). I want to use parameter in the select statement. I have searched various forums and didn't find any solution. My problem is that I want to get result from different databases.How can I use parameter to get data from 2 or more databases? I have two transformation.

  1. select catalog_name from information_schema.schemata where catalog_name like "%db"
  2. select data_name from ${catalog_name}.dbo.database_name

There is error that says Syntax error or access violation?

Solution

  • It sounds like you've already set things up properly. You have a job that has your first transform for query 1, and sends the output to the job results (row results). You then run transform #2 once for each schema found passing the schema name down as a parameter or variable.

    If any of this is incorrect, please specify what it is you're actually doing.

    I'm guessing the only thing you missed is checking the Replace variables in script box for the table input in transform 2.

    Again if this is not the case, please add more detail.