Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.

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

JasonB_WVU
5 - Atom

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?

4 REPLIES 4
joshuaburkhow
ACE Emeritus
ACE Emeritus

You'll likely need to just troubleshoot this one to figure out exactly what's happening...here's some suggestions

 

1. Send an email to support@alteryx.com so that you can start talking to an Alteryx support to ensure that it's not a known bug

2. I would check the logs (service and workflow logs) to see if anything is indicating what is happening

3. Try outputting a copy (renamed) of the file before it hits the email tool. If that file stays and the one that is getting emailed out disappears you have whittled your problem down, if not then there is likely something else going on. 

4. Another workaround option if you can do it, is to split the workflows. One to create the file and one to email it. That'll atleast get you back going while you figure it out. 

 

Best of luck, would love to hear what you find out!

 

Joshua Burkhow - Alteryx Ace | Global Alteryx Architect @PwC | Blogger @ AlterTricks
JasonB_WVU
5 - Atom

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.

 

JasonB_WVU
5 - Atom

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.  

raychase
11 - Bolide

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.