Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

ODBC connections, parameterize authentication

jdubw
5 - Atom

Hi

 

My team uses Alteryx desktop.  My goal is to parameterize the username and password for an ODBC connection in a standard workflow.

 

I created a macro using two text boxes and a text input.  The plan was to use the text boxes to update values in the text input.  I then tried to insert the macro into a standard workflow but could not attach the macro to a input data step with the lightening bolt icon.  Does anyone have an example of a parameterized database connection in a standard workflow?

 

 thanks

7 REPLIES 7
PeterS
Alteryx
Alteryx

Hi @jdubw

 

To set up a parameterized database connection you may want to consider creating a macro that has an Input tool in it that uses a full connection string, rather than the connection that Alteryx uses (the connection string would be specific to the database and driver that you are using, this is easily Google-able).  Then with Text Box interface tools you can update specific parts of the connection string like Username or password.  Once the connection string is properly updated with the interface tools a Data Stream Out tool could be attached to the Input tool to bring the results of the query back into the main workflow.  A screen shot below shows the rough set up of how this may look.  (you can ignore the errors, I was using a fake connection string)

 

Here are a couple links that may be helpful when setting this up.

Action tools

Intro to Apps and macros

 

Workflow in screen shot is attached, created with Alteryx 10.6

 

macro screen shot.png

 

Peter Stoddard
Manager, Technical Account Management
Alteryx, Inc.


sarawr29
5 - Atom

Hi @PeterS

 

Does this method work with other Providers? I am trying to use Teradata (as 'TDOLEDB' found here: https://www.connectionstrings.com/ole-db-provider-for-teradata/) and MySQL ('MySQLProv' found here: https://www.connectionstrings.com/mysql-oledb-mysqlprov/)

 

Every time I change the Provider in the string from 'SQLNCLI10' to 'TDOLEDB' or 'MySQLProv', I get an error message saying "The selected file is not a recognizable type:"

AlteryxError.JPG

PeterS
Alteryx
Alteryx

Hi @sarawr29,

 

You will first want to configure an Input Data tool to connect to the desired database.  After the tool is configured and connected successfully to the database you can then update the connection string as noted previously. 

Peter Stoddard
Manager, Technical Account Management
Alteryx, Inc.


sha1
5 - Atom

Hi @PeterS

 

   This is  very useful info.  I am working on a similar kind of scenario. My workflow has multiple input data and output data components. I want to parameterize the DSN  for all the data components, so that any user can enter their username and password once to run the workflow instead of clicking each In/Out data component and change the DSN. From the above solution, looks like, we need to add interface tools for each In/Out data component an user needs to enter multiple times their userids and pwds.

EX workflow DSN.JPG

Is there a way to change userid and pwd globally, so that it will propgate through out the workflow 

mompermj
7 - Meteor

i'm wondering the same thing. I have figured out how to get the password prompt to work, but now i want that password prompt to feed 3 separate queries. how would we do this without prompting for 3 separate passwords?

PeterS
Alteryx
Alteryx

Hi @mompermj 

 

Yes this is possible, after putting a Text Box interface tool on the canvas, just connect it to each input that you want to update, Similar to the example above. There will then be an Action tool that will need to be configured for each Input tool connection, but this would then allow you to enter the password once but have it update each input, assuming the password is the same for each connection.  The same can then be done for the User name if it needs to be updated as well.  (please excuse the red exclamation marks in my screen shot below, I just didn't configure the input tools).

 

Hope this helps!

 

update connection string parameter.png

 

 

Peter Stoddard
Manager, Technical Account Management
Alteryx, Inc.


mompermj
7 - Meteor

Thanks so much!!! i'll try that shortly.

one last question (slightly off topic) but one of my co-workers tried to use the application i made for a process and when he tries to login to the prompt with his Username/Pass it gives him an error for bad Username/Pass.

 

Is there something that i'd have to do so i could configure these processes to be run by multiple people? The ODBC connections are all the same name. I tried using the system ODBC name rather than a user defined ODBC. Any help is greatly appreciated.

Labels