SQL Stored Connections

Question

Hi All,

I have been tasked with automating ~40 workflows (that have DB connections to Oracle) to run on my company's Alteryx gallery via the scheduler. I have set up the data connection, in-DB connection file (using an SQL query), saved a few workflows to the gallery to test, and run them accordingly. The problem I am now faced with is the following:

If I make an adjustment to a query in the in-DB connection file, it will not cascade to other workflows in which I use the same file (and thus query). So if I make a change to a query in one workflow, I have to manually make that change in the remaining workflows in which that file (and subsequent query) is used. Is there a way to have Alteryx point to an input file (SQL file) that can be dynamically changed by users, and then have that file connect to the database (and write the query/pull the data) to initiate a scheduled workflow? This would hopefully allow me to change a query input that is used in ~15 workflows and have that change cascade to each subsequent workflow in which that query file is used.

Any suggestions are welcome as I have looked for countless hours on the discussion boards and could not find a solution. Thank you!!

JoeL · Accepted Answer

Hi,

This could easily be done by having a separate file define the query, making use of the Dynamic input In-DB tool.

First, a best-practice is to have an example of your query ready to go in an in-db input. Using the Dynamic Output, and some prep tools, we can get the formatted query output to file

Generate Query File

Query File Example

You can see that the file contains the in-db connection and Query (in this case connection is a file; could leverage NAS storage to make DB config file accessible to your Server)

Execute Query FileAnd here we are pulling in the file to execute the query using a Dynamic Input In-DB tool.

Attached is the whole process you see here.

On another note: you can also use this method of running queries from file to allow non-alteryx users make updates to production queries without touching the workflow. 🙂

Thanks