Switch between Stage and Prod data connections

[Mildman]

I have a workflow that reads lots of data from SQL db's and will run on schedule via Gallery.

Is there a way that I can "control" the environment that the db reads come from?

[Sebastiaandb]

He @Mildman ,

There are multiple solutions but (some are better but more difficult to implement):

1- Make a duplicate workflow and change all sources from PRD to STAGE via Options -> Advanced Options -> Workflow dependencies (here you can change them all to a different environment at once);

2- If you user admin controlled server connections, change the alias by connecting the input to interface tools (like action/list box), let the user select the environment on which the alias gets altered in the workflow;

3- Put all connections (both STAGE and PRD) in a separate containers and disable one of the containers based on the selection of the user on the gallery. 

You can make 2 and 3 also based on parameters from somewhere else. 

For our DEV and PRD environments we always just have 2 workflows (DEV and PRD), is easiest in term of maintenance.

Hope it helps you a bit.

Greetings,

Seb

[Kakuffo]

HI @Mildman  the best way to approach this is to have the DSN setup to on the workflow to match what you would want to read from in the Production environment. for example SQL_Prod is in use locally and you wnat this to seamlessly work in the server environment.

Create or adjust a DSN  in the server environment to the same name SQL_Prod. This way when Alteryx is looking for this db in the server environment the ODBC settings still point to a production DB using the same alias.

[Mildman]

I like 3, but when I schedule a workflow in Gallery, I dont have the option to set the parameters for each run.  I have tried using a workflow constant, envr = Stage or Prod, but I can get containers to open on close based on the workflow constant.

[apathetichell]

use Dynamic Input In-DB. Use a drop down or other way to change your DB connection name from your Dev name to your Prod name.

[n8rushton]

I had a similar issue in a prior job. Just throwing it out there, but I had built a Macro that had connection strings with in it. I just selected from the drop down which environment it went to and the macro would adjust the input tool to reflect.

[Mildman]

Is there a way to encrypt the password in the connection strings that are passed to the Dynamic input?

[n8rushton]

I don't think there is a way to pass encryption as a field, but if you do the con string as a macro the password would be saved encrypted.

[Felipe_Ribeir0]

Hi @Mildman 

You can use an adapted version of the input batch macro (https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...) to change password/username/stage etc. The encryption will not be a problem here, alteryx will handle this part after you replace the password.