Block Until Done in Macros - Possible Bug Not Respecting Order of Outputs?

[Scott_Snowman]

I have the below arrangement in a macro. 

We have a requirement to write the data to a SQL table after the workflow is run, but we do not use the Alteryx Output Data connectors. Instead, we use a command line interface to write to the table. The workflow accomplishes this by doing the following, where the markers in the picture correspond to the list below. Chained Block Until Done tools are used to ensure that the outputs happen in the below specific order.

1. It outputs the datastream to a .csv file with a dynamic filename based on one of the fields of data.
2. Sampling just one record of data, it uses the fields to construct a .sql query which loads into target tables based on the fields in the data. This query is constructed in a new field, and then only that new field is output to a .sql file using a .csv output style with \0 as delimiter.
3. That single record also is used to generate the contents of a .bat file, which is just a command to pass the .sql command to the command line tool and load the contents of the .csv file.
4. Finally, there is a macro on the last branch at the bottom which takes the FilePath of the .bat file as input and executes it via the Run Command tool.

The output order is usually just as shown in the picture above -- the .csv is written, the .sql is written, the .bat is written, and then the .bat is run.

However today we had an example of a workflow using this macro that did not respect the order of operations. It was trying to run the .bat file before it was written, leading to an error and halting the workflow. (The .csv and the .sql didn't get written either!)

Based on my understanding of chained Block Until Done tools, the macro shouldn't be able to try and run the .bat file before any file is written because the #4 macro that executes the .bat should be running only after #1, #2, and #3 in that order. Even more unusual, if I open the macro and do a Debug Workflow, but provide it with the exact same data input as the actual implementation, the output process works as expected. It's only when the data is outside the macro that this happens, and not for all datasets -- it just happened today while we've been using this macro on a daily basis for about 8 months.

Is this a bug? Or am I misunderstanding the actual performance of the Block Until Done tool?

[Laurap1228]

There are few things you can try:

If data streams are not running in the expected order, check the tool ID of the tools upstream from the Block Until Done tools. To check tool ID:

1. Select the tool.
2. In the Configuration window, access the Annotation tab.
3. Use ID to see what number is assigned to the tool. This number indicates the order in which the tool was added to the workflow.

If that doesn't work, I use the CreW Macro 'Wait a Second' to slow down the second stream, in order to be sure the 1st output finishes writing before the second one starts. 

[tferenc007]

HI ScottS28

I had a similar issue with an ordering in the past. Block Until Done hasn't worked for me properly (or I don't know how to use it). Instead of using Block Until Done I've started splitting workflows into a few smaller  and using Runner (CreW Macros) to run one after another and it works for me.

[Jonny]

Hi @Scott_Snowman ,

two cents:

1/ I am not sure it has to do with the issue but why are you keeping the anchor 1 empty on the two Block until done? could you try following the sequence? 1, then 2 and leave 3 empty if nothing.

2/ check this excerpt from the trouble shoot guideline from Alteryx: link: Block Until Done Tool | Alteryx Help

Could you check these two ideas and let me now if it helps?

cheers!

Jonny

[danilang]

Hi @Scott_Snowman 

The Block Until Done (BUD) usually runs the way it has for you for the past 8 months.  If the macro didn't run as expected today, it could be because of the incoming data.  If there are no records, the csv file won't be created.  If this is running on a server can you check the log for that job see if there were records?

Dan   

[john_watkins]

Between the block until done, and the Crew  Label/Parallel Block Until Done it seems that these don't always do what you expect them to.  I thought you could use these to control the flow of workflow, but seem to find cases where they do not meet my expectation.   It works great if you need to output to a file on anchor 1 and then send an email with the attachment, but for controlling other types on non-output.....   If anyone has a link to what these do specifically and the differences between the three feel free to post it up.

[Scott_Snowman]

Thanks for the engagement, everyone!

@Jonny - The anchor selection was arbitrary-ish, when building the workflow I went with whatever the auto-attach process linked to when I put the next tool on the canvas. I haven't found that this ordinarily impacts the output order; many of my workflows use only the 2 and 3 anchors, or even the 1 and 3. Indeed, the macro normally works, using 2 and 3 on some of the BUD tools.

Also I did check the IDs for the BUD tools, and their IDs do increase with branch #1 to branch #2 to branch #3.

@tferenc007 - I have tried to stay away from the CReW macros -- they're great, but they introduce an external dependency that I'd like to avoid if possible. I have found they generally solve some very specific use cases though, so I might need to rethink this decision. It just seems that Block Until Done should work this way without the external market.

@danilang - There were indeed records in the run, but what I did notice is that there were very few records compared to regular executions. It's not clear if that was what caused the issue, as the Block Until Done should be all-or-nothing to my understanding -- but it was the only notable difference I could see between the executions.

@john_watkins I've also found that occasionally the BUD tool behaves unexpectedly. I'd love to see a technical writeup of a comparison between this and the CReW macros.

[Scott_Snowman · Accepted solution]

Thanks to all who reviewed this -- as usual, it turns out the issue was something upstream and unrelated.

The workflow in which the macro was deployed had the "Disable all Tools that Write Output" checked. So branch #1, #2, and #3 couldn't output the necessary files for the .bat to run properly.

I appreciate all your insight though!