Block until outload complete

I am developing a workflow to grab data from a variety of sources, restructure it and load it to a local SQL server followed by a call to a SQL stored procedure to perform some calculations based on the newly loaded data, then bring the results back into Alteryx for further processing. Where I'm having difficulty is in scheduling the SQL stored procedure to operate after the data has been uploaded in full.

I have experimented unsuccessfully with the Block Until Done tool, given it will not wait until the full upload has been completed before commencing the stored procedure. Similarly, I can play around with the Wait A Minute tool but would regard it as a sub-optimal solution. With the size of the data set potentially varying considerably from day-to-day it will be difficult to nominate a single time to wait without waiting unnecessarily for long periods on many days of the week.

My reading of similar discussions is leading me down the path towards a macro involving regular status checks with the SQL server to determine if the upload has completed. Before I go down that route I'd like to explore any simpler options first. Can anyone suggest any simpler alternatives ?

Any ideas are welcome.

Output

Macros

Accepted answers

All comments

willemb

Try something like the below. You create a simple macro that's only purpose is to call the stored procedure. You then use a Block Until Done just before your Output that needs to complete first. You can probably skip the Record count that I show in the picture and just put your macro directly after the Block Until Done (output 2). The Record Count could ensure that you only submit one record to the macro though, so that in the case of a batch macro you don't call it thousands or millions of times.

dgibson

Thank you for the suggestion willemb. In some cases my uploads of input data to SQL server can comprise of several millions of rows and, although I'm using a bulk upload, can take 30 seconds - 1 minute to complete. Am I running any risk by using the block until done tool that, although the data has passed through output anchor 1, it has not finished loading to SQL server before the row count is completed by output anchor 2 and the stored procedure is called?

willemb

That sounds odd. Can you confirm a couple of things:

- your workflow settings aren't set to "Disable all tools that write output?" Your output tool will look like the picture below if that is the case.

- you're not seeing any results in the Results pane when you select the BUD output #1 anchor i.e. you're sure it's blocked at that point and not at the Output after the BUD.

- your workflow finishes successfully

dgibson

Odd indeed. I confirm yes to points 1 and 2. I have even attached browse tools to both output anchors and nothing is showing up. Regarding point 3, the workflow simply stops when it hits the stop until done tool, so the subsequent steps to call the optimisation are not activated. A 'workflow finished running in XXX time' message is displayed in the results window as per normal despite multiple downstream steps being skipped.

To add some extra context, my workflow is long with approx. 1400 tools prior to the BUD tool. From my reading about BUD it will attempt to determine if up-stream workflows are complete before it will activate. Could it be getting confused by an upstream workflow and giving up?

willemb

"Confused and giving up" should have resulted in an error in my estimation. It sounds like unexpected behaviour to me, so perhaps best to log a call with Alteryx support.

With so many tools in one workflow I would also consider breaking it down into its parts with macros. Not sure it will solve any problems in your specific case but I imagine it will at least make troubleshooting a little easier.

dgibson

Makes sense, I will log a ticket with Alteryx support and see if they can shed any light. Thanks for your input.

dgibson

As it turns out, Alteryx Support have confirmed 'the BUD tool is not supported due to the AMP engine'. In any case, a colleague has suggested an alternative approach using a dynamic input tool and, by combining it with the wait-a-second tool I have been able to get the job done. Although it's a bit of a blunt approach, potentially involving longer delays than are necessary, it will be good enough for now. Should the BUD tool become available with AMP I can explore using it for a more precise solution.

sarah_salisbury

Hi dgibson, I am also facing this same issue. Can you share a screen shot of your solution using the dynamic input tool and, by combining it with the wait-a-second tool? Thank you!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703