The process cannot access the file because it is being used by another process. (32)

A scheduled weekly complex workflow generating multiple tabs to a single Excel workbook on a company network drive using a service account.
Process time: Should be less than 4 minutes.

Notes:

1) No I can't share the workflow. Sensitive information.

2) No I have not been able to replicate it with fake data

3) This was not a problem in version 2021

4) Things I've tried related to the workflow:

4.a) Output tool only

4.b) Block until done (Placed before Output tool)
4.c) Block unit done (Strategically placed in middle of workflow steps)

4.d) Parallel Block Until Done (Crew Macro)
4.e) Wait Time Macro

4.f) https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Clear-the-output-file-before-running-a-macro-and-setting-up-an/m-p/1040648#M257628

4.g) I have tried every stack and mix of the above strategies in the gallery. Anywhere between 1-3 out of 7 total tabs will be successful. Results are not consistent.

4.h) Rebuilding the workflow in a new file is an unacceptable solution. Partially because the gallery does not permit version updates through new file overrides. Any new workflows are added as a separate workflow to the gallery and cannot be pinned to an existing workflow

4.i) Render tool - See Patch notes - TDALI-4185 DE34172 (Corrupt files - known issue)

5) There are some solutions that might work which will significantly increase the workflow processing time. I ignore these as they are not practical solutions.

6) AMP Engine is turned "Off" - Running on Version 2022.1

For designer specific functionality, option 4.f) worked flawlessly. Edit* Okay... not flawlessly... I saw this fail 12/19/2022. Uhg

For Gallery specific functionality, all options failed.

It appears as though Alteryx may have prioritized efficiency over priority in some of it's functionality. Not sure if any one ever notices some tools on their designer turning on before it's the tool's turn... (Indicated by the green box highlighting the tool. The gallery appears to have a different priority setting than the designer because my workflow succeeds when run off the designer but the same workflow fails when run in the gallery.

My opinion is the output tool should have a configurable reset when it has this specific error so that it can try again

Issue: The process cannot access the file because it is being used by another process. (32)

Workflow Image Below: I'm just showing two strategies here. 4.b) and 4.f) - 4.b worked in 2021 and does not work in 2022. 4.f) works in 2022 designer, but not gallery.

Gallery

Server

Accepted answers

All comments

MatthewO

@PeteLacz Alteryx processes data in "packets" which is why you will sometimes see a downstream tool turn green before a preceding tool has finished. That said, there are some tools that are blocking, meaning that they require all of the data to arrive before passing it to output. Block Until Done is an example of these tools, but I think the issue is how you have them sequenced. Excel files do not allow more than one process to write to them at a time, so it necessary to sequence your workflow so that only one sheet writes at a time. I have attached an example workflow that demonstrates how you can structure Block Until Done tools (in conjunction with the Count Records and Append Fields which are also blocking tools) to accomplish this. I am also including some links below with more detail that expand on these topics.

Alteryx Engine and AMP: Main Differences

The Periodic Table of Alteryx Tools

Output Multiple Excel Sheets.yxmd

PeteLacz

@MatthewO Thanks. Per my notes, this solution pertains to step 4.c) Block unit done (Strategically placed in middle of workflow steps)

This did not resolve my problem. Also, I would argue that this step turns a complex workflow into a puzzle when deciding placement which from a user experience sounds like a terrible idea. In 2021, block until done worked in such a way that each block until done tool even though separate, waited for it's copy to finish before executing. This allowed me to rely on tool ID or first come first serve operation to determine sequence. But... it waited... and now it is not waiting for it's brother or sister tool path to finish before starting it's own.

Also, macro's are essentially block until tools in their own right. Which is why solution 4.f works in the designer... but for whatever reason. It is not working in the Gallery.

MatthewO

@PeteLacz there are a number of factors that could be impacting the workflow behavior. You mentioned using 2021, and I'm curious which version you are using. With the release of 2021.4 specifically, the AMP engine was enabled by default which processes records differently than the original engine. The Server can also be configured to run the original engine only, AMP only, or both. It may be worth working with your Server admin to confirm these settings.

I agree with you on the added complexity specific to outputting Excel files. That said, I still would have expected the steps outlined above to work. My next thought is that it may related to how the data streams are connected in earlier steps. If I am understanding your explanation of the Block Until Done tool, it is incorrect. These tools would only achieve that behavior if they were chained together properly. From the screenshot you shared, I would not expect it to work in the intended manner. If it worked in a previous version, and no longer does, it could again be related to the engine you are using and how records are processed. A simpler solution is to configure the Output Data tool take the sheet name from a field but I assume the data streams have different structures, which is why they are being output separately.

I think I may have missed what the macros are doing in your workflow. If the write step is occurring in the macros, then you may also want to confirm the macro type. A batch macro would be blocking, but a standard macro would not be.

PeteLacz

Thanks @MatthewO

Version is 2022.1 - i did not think to add a note about AMP engine. So I added one to Note 6 in the original post.

But to answer your question, yes, it is turned off on both the designer and the server.

Now... as for the block until done tool, the way I explained it pertained to the way it worked in 2021 with all my workflows. That is no longer the case in 2022, and I am painfully aware of that because many of my workflows broke because of the change. That said, yes, it is the way it worked in 2021. It may not have been intentional, but it did work. And spectacularly so. So much so that ALTERYX should consider adding a tool that does exactly that.

As for the strategic placement of the block until done tools, you will just have to trust that I experimented enough with it using your strategy. It is a complex workflow, so I am not going to rule out the possibility of user error. But I've tested enough to prove that this solution is not ideal.

As for the standard vs batch macro, I will have to check. TBD : ) See attached for the macro I am using.

Excel Output different formats to multiple Sheets in one file (1).yxmc

MatthewO

@PeteLacz I looked at the macro you shared and I think I see the culprit. You are correct that batch macros are blocking and wait for all results to arrive before being output. In this macro a dummy record, Field1, is leveraged and immediately output for the purposes of chaining workflows together. I believe that the record is being output before the data is completely written to the file, allowing the workflow to progress to the next macro in the sequence. This occurs because the output is not being updated by the macro configuration and is not dependent on other steps in the macro.

Please note that I have not tested the attached modified macro, so please do so in a non-production instance, but incorporating the Block Until Done tool within the macro may resolve the issue. A Count Records tool is used to populate 1 row of data for output so that the macro can be sequenced.

Excel Output different formats to multiple Sheets in one file MODIFIED.yxmc

PeteLacz

@MatthewO

No Dice. That is a better idea though. I see what you did. I still had three tabs fail unfortunately. I really want someone to fix the output tool to account for situations like these so it can be addressed for the next update. This is such a basic and critical process to write multiple tabs to an excel. But at the end of the day this is just as much a gallery issue because these jerry rigged solutions should theoretically work.
But Matt, I do suspect that once the render tool ( TDALI-4185 DE34172 (Corrupt files - known issue)) is fixed, it may resolve this issue. Assuming it will work the way I think it does. I could convert all 7 tabs to tables and run them through render and output into an excel file on 7 tabs.

As for the parallel processing of packets to take advantage of efficiencies I understand why Alteryx does this... Buuuuut, they need to give the user the power to control packet processing order. EVEN IF, it slows down the overall process, particularly for diagnosis of situations like these.

haphan_tran

I took a deep dive in this issue in this post. You can take a look, I think it will help you solve your issue
Writing to multiple Excel Sheets in the same files... - Alteryx Community

MatthewO

@PeteLacz there is a new Control Container tool that was included in the 2023.1 release that may be helpful for you. This is intended to help resolve the writing conflicts you observed in this instance. Check out the release notes for more information:

https://help.alteryx.com/release-notes/designer/designer-20231-release-notes

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703