This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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?