Block until outload complete

[dgibson]

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.

[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! 

[Lockard]

Hi Sarah,

I suspect you have figured out a solution by now, but thought I'd share a similar solution that "so far" seems to work for this scenario.

In the left container I am making local Alteryx Databases and need processing in the right container to wait for the local Alteryx databases to be built before processing the workflow.  To accomplish this, I'm using Block until done and a count of the records to delay "future" processing.

I was worried that the "read" or the local databases in the right container "could" start as it is not directly blocked by anything. I have tested this small example using the AMP engine a few times and it seems to work for my purposes.  I'm building out my complete solution and will post if I have issues.

Hope this helps others.

EDIT - There is some interesting Alteryx behavior that I can't fully explain at this time.  When I tried to add an Archive container before the Build Local Tables container in my above test workflow, doing a similar block until done did NOT properly control the sequence as I intended.  I observed Alteryx starting with the Build Local Tables container.

For fun, I copied my new Archive container to a new workflow.  Then I copied the Build Local Tables container followed by the "Wait" container in three separate copies.  (I have to reconnect the streams that flow between the containers)  Now, Alteryx starts with my new Archive container and appears to sequence the containers as I want (and need).

I'm assuming there is some "natural sequence" to attempt to run the workflow in the order the workflow was built that combines with the Block Unit Done to generate this behavior.

Hope this Helps and I also hope this behavior keeps working for me.