Dynamically change ODBC connection string for output

[apozzy1]

We have an environment where Alteryx workflows populate a PostgreSQL database using an ODBC connection. We want to be able to move from dev to test to production without having to update the output tools, using relative paths on the input tool, passing the connection info downstream. So we have a folder structure where we store our workflows, and in each of those folders is an excel config file which contains the database name for each environment. All three databases are on the same server.

I seem to be able to pass the database name, but when I switch environments I get an error about cross-database references. I think the problem is that the ODBC string is still pointing to my dev database, and I'm passing the test database name to the output, and it seems to be getting confused. It seems that what I need is to be able to update the ODBC string as shown in the upper left of this screen shot. Can this be done?

Thanks!

[MarqueeCrew]

@apozzy1 have you considered using global constants?  The help page actually uses an odbc string as an example!

From the configuration panel (Workflow) you can press the + sign and create a constant of ODBC_String (as shown)

Then you can update (one time) your output tools to read this string (instead of your odbc connection string): 

%user.ODBC_String%

That might solve the issue for you.

Cheers,

Mark

[apozzy1]

Thank you for the quick reply. I'm not quite understanding the whole picture yet, although I'll go in and play with it shortly. The one thing I'm not sure of, is how that constant gets updated when I switch between environments. Basically, whichever folder I save the workflow in will have a config file which gives the ODBC string, and then I need to pass that to Alteryx, so that we don't have to touch any tools when moving between environments - we want to just be able to copy the workflow to a folder and it will figure out the rest, based on the config. Does that make sense, and is that what you're talking about here and I just don't get it yet?

thanks,

Alex

[MarqueeCrew]

@apozzy1,

One thought is this:

Place your Workflow into a staging directory for change management.

Write a workflow that reads the configuration for "prod" and run it against the workflow by reading it in as a flat file.  The workflow will edit the XML and save the workflow to the "PROD" environment.

The workflow is STATIC, but no configuration changes are required.

Another thought is:

Setup named connection aliases that are identical on all environments and deploy the same workflow onto different servers.  If you're using different servers that is.

Cheers,

Mark

[amti]

Seems fine if we're using Workflow only, but if you want to do it inside batch macros, you can't really use wf user constants, can you? 

Also not ideal to pass encrypted password string here, I believe.

Is this the usual workaround?

[soumyasfeb8]

 I tried to apply the same logic for parameterizing OLEDB Connection but getting an error "Error: Output Data (2): Error opening connect string: Microsoft SQL Server Native Client 11.0: Login failed for user******". Is there any way to resolve it?