community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Multiple SQL statements (stored procs) in a workflow

Meteoroid

Hi--my company just acquired Alteryx. Before, I would schedule my stored procs to run daily on a desktop (thank the lord for Alteryx + Alteryx server). A lot of these procs need to run in a particular order. Now, we're trying to schedule everything on Alteryx and get rid of our procs.

 

That said, I don't want to translate all of my procs into the Alteryx nodes, but I do want to make it easy for my colleagues to read my workflows. What I would like to be able to do is schedule multiple queries (what used to be my stored procedures) sequentially in a single workflow and label the queries, so my colleagues understand exactly what they're doing. Basically like a SQL command node that you can find in SSIS.

 

Right now, my understanding is that there are two ways to do this, but I don't like either of them. The first is to take an input node to connect to a DB, set the query to Select 1 or something similar, and then use the pre/post SQL to run my code. If I separate the code with a ";", they can run sequentially. However, this is a problem because if I string the code together, we're talking like 20 pages of code per process, and it's unmanageable for anyone not familiar with it. I could run a string exec stored procs statements, but I'd have to keep my procs, and I'd like to remove them from our DB.

 

The second is to schedule each stored proc on a separate workflow, then use the Conditional Runner + Block Until Done to make the workflows flow in order. This way, I can label every step of the process, and each step can have a manageable amount of SQL code. This is better than running 20+ pages of code off an Input line, but it's still painful because I need to save every step in its own separate workflow before combining them into a master workflow. If someone needs to edit it later, they'll need to go into each component instead of just opening the workflow as is.

 

Is there a better way to do this--maybe using the In-Database tools, or something? Sorry if this has been asked before.

Alteryx Certified Partner

Hi @laney1

 

I can undersand your requirement. I am coming from SSIS and DTS background. Alteryx is not meant for running stored procedures against a database. This is like having one SSIS's "data flow" task. The only way is to chain them in order to make it run one after the other.

Meteoroid

Thank you. So there's no other way to run SQL statements sequentially other than using the input node, or linking multiple workflows? What are the best tools for chaining--are there others than the ones I listed above?

Atom

A "Block until Done" macro might help you in ordering the jobs.

@laney1  Hi, I also have lots of Sql queries need to be run in sequential order. e.g.

firts create table

then insert values

select query

then update query 

in this order. did you manage to do it? Please help

Atom

A "Block until Done" macro might help you in ordering the jobs.

Labels