Solved! Go to Solution.
Hi,
The attached workflow isn't exact to your example (it currently outputs to individual workbooks) but you should be able to tweak it relatively easily.
This workflow takes advantage of a Batch Macro to execute SQL against a specified server. You will need to update the macro so that it points to the correct SQL Server. It then outputs based on a filename which is passed in from the workflow. You could tweak this so it only updates a sheet name, or tweak the formula so that it creates the sheet name but keeps the filename consistent.
I hope this helps to explain the process/concept!
Thanks for the help. Unfortunately, I'm unable to open it to look. I'm on version 11 still and I'm guessing you may be on a more recent version.
Ah, nothing a little xml editing won't fix . . . ha-ha. I got it to open and taking a look. Will hit you up with any questions if I have any. Thanks again!
Awesome, I got it working, for the SQL execution at least. After playing around with it I realized in the input data node you need to add a generic sql statement and in the macro action node set the replacement of that generic sql statement to the one your batching in.
Much appreciated....now to work the single Excel workbook package :).
Hi Jmstampe,
Can you make your solution a little bit clear, i mean can you please help us with the steps that you have followed
to make the sql queries run, Any reply for this would be really appreciated.
Thanks in advance
Sure, I’ll see if I can find the original macro workflow I created for this and try to post some screen caps. Unfortunately, due to line of work I’m unable to send.
I think I ended up going a different route on the project and loaded all my scripts to input tools as I only needed to run 8-10 and some overlapped in data/fields. Still a cool workflow setup regardless.
I can’t seem to find the macro but I’ll keep looking. In the meantime it sounds like you would want to use a directory tool, to get your list of sql scripts then create one batch macro to drop the scripts into a table and another batch macro to read each script in either a dynamic input or normal input tool (replacing the table or sql with your sql in the actions menu).
I’ll keep looking though.
Yeah, Thanks a lot for your help!
I have done the same as you suggested, i have created a directory tool and placed all my SQL files in it and
fed it to the batch macro and that seems to be good,i'm able to run my SQL files one after the other dynamically
with the help of control parameter tool followed up with the update tool.
one more question would be can i run my SQL quires all at a time instead of running it one by one.
If possible could you help me to get this.
Thanks in advance.
Great news! I’m glad that worked.
As for running all at once, I’m not aware of Alteryx being able to do that unless you were to create one combined query from all the scripts and loaded that into an input tool. That’s one of the reasons why I ended up going a different route with my original workflow was that I was able JOIN and UNION a lot my queries together dropping the amount scripts I needed. If you’re good with SQL I say go that route.