Hitachi Vantara Pentaho Community Forums
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Input Table Step - Problems using Parameter for ODBC DSN Name

  1. #1

    Default Input Table Step - Problems using Parameter for ODBC DSN Name

    Guys,

    My requirement is to dynamically provide the value of "ODBC DSN Source Name" at runtime.

    I tried using two different transformations within a job, one using "Set Variables" step to set the value of a variable DSN_NAME and then in second transform, I refer to it within Input Table --> Connection --> (Edit) --> ODBC DSN Source Name = ${DSN_NAME}.

    However, it doesn't work.

    Any help from you all expert folks would be highly appreciated.

    Thanks in Advance,
    Pentaho Newbie.

  2. #2

    Default

    Whats the exact error you are getting?

  3. #3

    Default

    Hi,

    The error message I get is:

    [Microsoft][ODBC Driver Manager] Data source name too long.

    I guess the variable ${DSN_NAME} is not getting replaced. Am I missing something? I have read through allmost all the post related to this BUT cannot undersatnd why this isn't working.

    Again, any help would be greatly appreciated.

    Thanks,

  4. #4
    Join Date
    Mar 2010
    Posts
    159

    Default

    Are you explicitly passing DSN_NAME into the second transform as a parameter?

  5. #5

    Default

    Hi,

    I am not explicitly passing DSN_NAME as parameter. My scenario is :

    I have two transforms:

    1. In the first transform, I get the DSN NAME and then do "Set Variables" step to set variable DSN_NAME, with value.
    2. In the second transform, I directly try to access it's value in "Table Input" step - using ${DSN_NAME}, but apparently it doesno t return anything.

    Earlier, I tried explicitly passing parameter using "Mapping (sub-transformation)" step in the first transformation and then using "Mapping Input Specification" step in the second transformation. In this case, if I attach "Mapping input specification" to a text file and see the value of DSN_NAME, I can see correct value. However, when I try to use the same variable in "Table Input" step -- Connection ---Edit ---ODBC DSN Source Name, it does not work!

    Kindly help.

    Thanks for reading.

  6. #6

    Default

    Can Anyone please help me with this one?

    Senior members, kindly suggest.

    Thanks.

  7. #7
    Join Date
    Mar 2010
    Posts
    159

    Default

    In the settings for the second transform, add a parameter named DSN_NAME for the transform. In the dialog for widget calling the second transform, pass in parameter DSN_NAME with value ${DSN_NAME}. See if that helps.

    Regards,
    Jeremy

  8. #8

    Default

    Thanks Jeremy.

    I made changes as per your suggestion but it still does not work.

    A strange thing I observed though - for the second transform - when I do Edit --> Set Environment Variables (in spoon), I see the variable DSN_NAME there. Then I set the value of DSN_NAME to a hardcoded value. Under such a circumstances when I try to run the second transform, it runs perfectly fine and the Table Input step resolves the value of ${DSN_NAME} correctly. However, when I run the same transform (without any changes ), using the job that has First transform that sets the variable DSN_NAME and then the second transform as above, stragely the second transform fails saying no DSN_NAME.

    So the same trasform when run isolated runs perfectly fine BUT when run as a job it fails! I think I might be doing something basic wrong here.

    Could you all expert please help.

    Thanks,

  9. #9
    Join Date
    Apr 2008
    Posts
    4,684

    Default

    Perhaps post two sample transforms and a sample job of what you are trying...
    Put all three files in a single zip.

    The community is really good at working through sample files and providing feedback.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  10. #10

    Default

    Thanks Gutlez.

    Please find attached zip file that contains

    Three transform files:
    Step1.ktr --> connects to sybase database to retrieve DSN name/s
    Step2.Ktr --> Sets variable ODBCDSN for each incoing record and executes Step3.ktr
    Step3.Ktr ---> Connects to a Sybase databse pointed by ODBCDSN and extract the data to an excel file.

    So my problem statement is - for each record retrieved in Step1.ktr, I want to connect to the corresponding database and extract the contents of given table into an excel file.

    So if Step1.ktr retrieves say 20 records, then for each record I want to connect to the corresponding Sybase database and extract the data.

    Kindly help.

    Thanks.
    Attached Files Attached Files

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.