Dynamic SQL Connection

[mg4444]

Hi, this is my first ever post on here. Really thankful for the community on here! I'm on to ask my first ever question.

BACKGROUND:

We have 2 SQL Databases with identical Schemas and field names- UAT and PROD.

In Alteryx our developers cannot query PROD directly and can only do so via Alteryx Gallery.

This means all of out developing needs to be done referencing the UAT data then when we promote to gallery for scheduling etc we need to change all the connections to point to the PROD database.

QUESTION:

Is there a way to have write 1 SQL Connection/query where the exact connection changes dynamically dependent on where the workflow is run from?

I wont muddy the waters with EVERYTHING we have tried but we thought we were close with the Dynamic input tool using a formula to drive the connection based on the 'Engine.WorkflowDirectory' the query was being run from but to no avail-we get a 'no table chosen' error. We have tried changing the 'Action' drop downs also.

* 'USER.PROD' & 'USER.UAT' are constants with the connection strings for each DB.

Below is a basic visual of what we've had to use until we can get this sorted-it involves us changing the connection to the PROD input when we save to the gallery. (20 or 30 tables)

[atcodedog05]

Hi @mg4444 

In the dynamic input tool did you click on edit and enter the select query similar to input tool? 

[mg4444]

Hi @atcodedog05, that is completed...just scribbled out for security

The query needs to remain the same...just the connection needs to change.

[atcodedog05]

Hi @mg4444 

Try putting input tool in a batch macro and changing the connection string in input tool using control parameter + action tool.