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

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?

In Database

Database Connection

In-Database

Accepted answers

chris_love

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

All comments

dataMack

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.

chris_love

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

BenG

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.

Philip

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.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335