Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.
SOLVED

How can I run a single workflow against two different database instances?

Highlighted
Quasar

Hi Community

 

We have a process that is run against a test environment SQL Server database and a production environment SQL Server database. I would like to only maintain one workflow for both since they use the same process.

 

The workflow:

1. Has In-Database Connectors to eight tables using file connection info.

2. Is run on a daily basis.

3. Is currently a desktop run, but will be moved to Alteryx Server to run on a nightly basis.

 

Has anybody come up with an idea of how to run a single workflow against two databases with separate connection files in a situation like this?

Highlighted
Quasar
Quasar

There's probably a couple ways to do it, but when dealing with environment switching, I use containers, which can be turned on or off completely based on an Interface tool + action tool.  In the workflows the I publish to server, the user gets a drop down that asks where the want to publish data (dev or prod) and based on that selection I turn on the appropriate container while disabling the other.

Alteryx Certified Partner
Alteryx Certified Partner

The way I'd do this is to have a local file containing the server connections, turn the workflow into a batch macro and then use the file to dynamically alter the paths.

 

In that way you can have different local files on each server but the main workflow and macro can stay the same.

 

Chris

Highlighted
Alteryx Alumni (Retired)

I've always liked the idea of having input macros that point to different connections.  If you create two macros with the same name, and install the QA version into one account/environment such as a specific QA user, and then install the production version into the account for whoever is going to run the production version, the right one will be detected based on the environment.  Same workflow, but the macro (myMacros\connectionMacro.yxmc) would be resolved based on the environment.  May be more complex than you're looking for.  If you think in terms of Desktop/Server, this works fairly well.  If you're talking User1 and User2, this is a little harder.  I don't think it works at all if you want to run both as the same user on the same machine.

Highlighted
Quasar

Thanks everybody! After messing around with the different solutions, ended up going with the Batch Macro approach with a text input of connection file locations and database names brought in through Interface Control Parameters to update all the In-Database tools. It seemed the most flexible given that server changes and database names may change in the future.

Labels