Output Error: Unable to create backup of ****.xlsx to ****xlsx.bak: Access is denied. (5)

[rshenoy]

I have a workflow that outputs to 5 different ranges within the same .xlsx file.

I get an error that says "Unable to create backup of ****.xlsx to ****xlsx.bak: Access is denied. (5)" only when running the workflow on the Alteryx scheduler. When I run the workflow manually, it runs successfully without any errors. 

I have tried several things in attempt to solve this issue but none of the following have seemed to work:

• Wrote a script that runs before the workflow starts, that deletes the .xlsx.bak file 
• Added Block Until Done tools, so that all outputs will not output at once
• Added Wait A Second, so that all outputs will not output at once
• Broke the workflow into 5 different workflows; one for each output
• Changed the Runtime configuration, so now "Use AMP Engine" is enabled
• Created a new .xlsx file and file name for the output. This resolves the issue for one scheduled run, but I continue to get the error for subsequent scheduled runs

Any thoughts as to what may be causing this error or what could resolve the issue?

[mceleavey]

Hi @rshenoy ,

this sounds like a permissions error. Is the user that is being used on the scheduler the same as the user that is logging into the machine, or are you using a service account?

If the user on the Scheduler is different, then it looks like that user does not have write permission to that folder. For example, if you are using "My Documents" then only your username will be able to write to that folder.

To check this, change the user running the scheduler to your username and try that, or change the folder to which you are writing to a common shared folder.

M.

[ChrisTX]

If you don't have security permission errors.... you may be running into a write-contention error, where Alteryx is trying to write to the same Excel file at the same time.  I had a workflow that ran OK when I was writing to an Excel file on my desktop, but generated errors like "cannot create backup" when writing to a network folder (likely because of a slower write speed) .

It's very frustrating, and solid solutions are rare.

In this post I uploaded a macro and sample workflow I use to avoid errors when writing to one Excel file.  I *still* get occasional errors with this approach, but they are rare.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Clear-the-output-file-before-running-a...

And in this post I explain how to configure multiple calls to the macro:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Clear-the-output-file-before-running-a...

And if you'd care to add a Like to my related Idea, where I'm asking Alteryx to help make it easier to write to one Excel file without write-contention errors:  https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Enhance-options-for-Excel-output/idi-p/84326...

Chris

[EdP]

Please see the following as well: https://knowledge.alteryx.com/index/s/article/Error-writing-Excel-file-to-Shared-Drive

[mzak89]

Hello!

I was able to fix similar issue by closing the output tool inside the batch macro like in the attached file.

With the file name as the control parameter. This approach ensures that the records are being processed one by one and worked for me.

Kind regards
Mateusz

[CharlesW]

Hi all, this was supposed to be fixed in the new version with Control Containers,  unfortunately it still happens. 

Runs fine sometimes while the first time I try to run on the scheduler it fails because it cannot create the .bak file. 

• I've changed from using parallel block until done (which doesn't work using AMP engine)
• I use control containers to control and write only one tab at a time. (which requires you to enable AMP engine) 
• I run powershell after every run to remove the .bak file
• It's not a permissions issue because if it can write the excel file in the first place it could write the .bak file. 

So at this point, I just cross my fingers and hope today it my lucky day. 

[ChrisTX]

When you link Control Containers that write out Excel sheets, have you tried adding a CReW_DelayASecond macro?

I haven't had any problems after I started using Control Containers.

[CharlesW]

No sir, not yet.  I removed those because they always cause error on save to server and shouldn't need them when you are using control containers and technically Crew macros are not supported on server.

Thanks for the idea though!

I'm hoping the issue was the .bak file was being deleted before it was finished.  - Which also shouldn't happen since the even was set to "after run" 

So I've changed my powershell, to target a parent directory and just delete recursively through the child folders. It takes a little longer for powershell to run through all the child folders (10 seconds).  

I'm not sure if that'll fix it, Because, it usually finishes locally, It's scheduled for 4:30a tomorrow.  We'll see how it goes........

It's when I run these workflows on server using a schedule that things seem to get unpredictable.

[EdP]

Hi @CharlesW 

Please see https://knowledge.alteryx.com/index/s/article/Error-writing-Excel-file-to-Shared-Drive

We're finding some environments have replication or virus scans that run and lock the BAK file, which causes Designer to error on the file.  Adding a delay alleviates the issue.  I suspect the Powershell script is adding a similar delay as the DelayASecond CReW macro and serving the same function of giving a moment for the background process to complete the replication or scan and unlock the file.

If you'd like to open a case, you can ask it be directed to me, Ed Phelps, and I'll be happy to work on this issue with you.