Dynamically change ODBC connection string for output

Question

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!

dynamic odbc.PNG

soumyasfeb8 · Answer

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?

amti · Answer

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?

MarqueeCrew · Answer

@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