Scheduled Workflows Output to Excel - .xlsx.bak Created But .xlsx Not Always Created

Question

I have a lot of workflows that output directly to Excel on overnight schedules.  Some have worked and have not been touched for months.  Recently (maybe related to server upgrade 2020.3? two weeks ago) some workflows (e.g. those that email previously output files to colleagues) have started failing because the xlsx outputs from earlier workflows do not always exist.  Alteryx is creating the expected .xlsx.bak file but the .xlsx file itself is missing from the directory.  If I later run the same workflow manually (either through Designer or from the Gallery) with the exact same data, the .xlsx.bak AND the .xlsx are created as expected.  And, to be clear, the scheduled workflow that creates the .xlsx.bak and xlsx shows in the Gallery that it ran successfully.

Thought it might be schedules running into each other and unable to write to the same directory at the same time but even after separating schedules for multiple minutes (most workflows take less than 15 seconds to run), the .xlsx is there somedays and randomly not there other days.  The net net result is somedays the emails send with the xlsx attachments and other days they don't send at all due to "The system cannot find the file specified."  Have I found a file output bug?

raychase · Answer

I'll chime in here:

Are you using two separate data streams within the workflow to write to different sheets within the same Excel workbook, or are you achieving this using report bursting (ie. supplying a full path within the data, and configuring a single output tool to use this field)?

You mentioned your workbook lives on a network location.  I assume this is a network drive, and not a SharePoint site?  I've run into plenty of issues when trying to write to the same workbook in short-succession if it lives on SharePoint, but I've always had success using a network drive.

Have you considered trying to output the data locally (on the Alteryx Server itself) and then using a .bat file to copy the completed workbook over to the network share?  If your issue is being caused by an ever-so-slight latency issue between the host and the network drive, then this could potentially alleviate your issue.

If this is, in fact, an issue presented with version 2020.3, then I fear I'll be in the same boat shortly.  I may actually run some tests in QA to see if I can reproduce your findings.

JasonB_WVU · Answer

Putting a pin in this.  After connecting with Alteryx support, the answer is my situation (trying to write to two sheets in a single Excel file in a single workflow) is an edge case and "was never an intended or tested outcome as there can be a bunch of issues writing to a network location in multiple sheets".  The only (solid) solution is to create two separate workflows that run in succession to output to sheet1 and then to sheet2.

JasonB_WVU · Answer

Thanks for the tips and input.  I'm going to pull in the Alteryx support folks but wanted to add a couple additional notes that may help others experiencing a similar issue.

To clarify my original post:

* Scheduled workflow A outputs A.xlsx (and creates A.xlsx.bak)
* Scheduled workflow B outputs B.xlsx (and creates B.xlsx.bak)
* Scheduled workflow C emails A.xlsx and B.xlsx

C fails when B.xlsx doesn't exist.  B.xlsx sometimes gets "recreated" (overwrite sheet) and sometimes doesn't (it always should; not a data issue) but B.xlsx.bak always exists and has the expected 'Date modified' as if the B workflow ran without error and recreated B.xlsx.  Details in Gallery (green check) indicate B ran without error.

Update: workflow B overwrites two sheets (B1, B2) in B.xlsx.  My hunch is the problem is in that small gap when Alteryx makes B.xlsx.bak to overwrite B1 then almost immediately does the same to overwrite B2.  Alteryx believes everything completed successfully (green check) but the network server being written to isn't processing the backup/write/backup/write commands fast enough and I'm left with an updated B.xlsx.bak but sometimes no B.xlsx.  I've tried using Block Until Done and some other approaches to create a delay between the overwrites of sheets B1 and B2 but nothing has worked (consistently) so far.  I never had this issue prior to the recent Alteryx server upgrade (coincidence?) but may have no choice other than separate B1 and B2 into separate workflows (at least for the short term).

Thanks again for any and all input and guidance.