How to use different parameter sets at run time

[ujala]

Hello all,

I have a requirement like to get the query from one ref table from one database and the same should be passed to ODBC stage in a job and there after it fetches the data from database and loads into a dataset

My problem is i have different parameter sets defined for all sources
(Note : I can't use value file of parameter sets), it contains DSN,username and password, so accordingly my sources and dsn,username passd will change anyways i have a column defined the ref table which says which DB.

So how to generalize the job such that it takes the required dsn and loads the data into a dataset

[jwiles]

Based on your description, it appears that you have tried to use multiple parameter sets as a replacement for value sets. That won't work because you can't change change or specify parameter sets at runtime--the parameter set being used is essentially hard-coded into the job design. You DO have the ability to change or specify parameter values at runtime. When using parameter sets, the proper method to accomplish this is to use value sets...that is their primary purpose.

You can use value sets now, just create some for the parameter set you have selected for the job and supply the correct one at runtime. If there is some concern about credentials in the value set files, make sure you use an encrypted password string.

Regards,

[PhilHibbs]

If there is some concern about credentials in the value set files, make sure you use an encrypted password string.

Encrypted values are trivial to decrypt, the only advantage that they give is that they are replaced by ***s in the job log and so can't be viewed in Director. Anyone who can access your value set files can access your passwords.

If the requirement is to fetch the database name from a table and then connect to that database, then you will need to query the database name outside of your query job, for instance in a Sequence Job, and pass that database name as a parameter to the job that gets the data. We shell out to the IBM DB2 CLP to run queries in Sequence Jobs, I'm not sure what tool you would use if you need to use an ODBC connection.