Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Is there a way to create new/select predefined database connections dynamically?

  1. #1
    Join Date
    Mar 2017
    Posts
    7

    Question Is there a way to create new/select predefined database connections dynamically?

    Hello Pentaho Community,

    I've gone through many posts in the past few days, however was unable to find a suitable solution for my challenge. I hope some one has done something similar in the past and would be willing to share...

    I have a requirement to get hundreds of tables residing on different databases flattened and shared with other system. Instead of creating a separate transformation for each table i need to export, i created one job that loops through a driver file that contains all parameters including SQL, output file, etc for all tables; that works just fine. The part that i'm not sure on how to implement is how to create new/select pre-defined dynamic connection from same driver file or list of existing shared connections. When the process runs, it goes though each record on the driver table and create a text file. I'm looking for a way to pass either all components of a connection as a variable to create that connection at run time or select pre-defined connection based on driver entry. I'm using Table Input control to pull the data from a SQL passed via a variable and need to be able to create/select a connection that will correspond to source table residing either on SQL Server/Oracle or Sybase databases. We have shared kettle.properties file on each environment to maintain corresponding connection properties. I think, it would be a simpler task if the data came from one database, but i cannot think of a way to create a connection based on database type.

    PDI 5.2

    Appreciate your help in advance!
    Last edited by gwa__357; 04-11-2018 at 06:24 PM.

  2. #2
    Join Date
    Aug 2016
    Posts
    143

    Default

    Database name, IP, password, username and port can be set as variables. Does that help?

  3. #3
    Join Date
    Apr 2008
    Posts
    4,541

    Default

    Quote Originally Posted by gwa__357 View Post
    but i cannot think of a way to create a connection based on database type.
    And you're not likely to be able to, because the SQL statements between MS SQL and Oracle are slightly different, and PDI has been programmed to use the different "dialects"

    You could sort your input to sift into two lists: Oracle & MS SQL. Then you can run distinct processes for the Database types, using variables for everything else

Tags for this Thread

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 - 2017 Pentaho Corporation. All Rights Reserved.