SSIS For Each Cannot Access File - Possible Double Hop

Question

I am attempting to run a for each loop that will refresh an excel file with C# Interop assembley and then email out. The package runs on my machine through visual studio perfectly. If I deploy to either my local SSISDB or the target server SSISDB, it fails with the following error when run from SQL Agent...

Microsoft Excel cannot access the file XXX. There are several possible reasons:

1. The file name or path does not exist.
2. The file is being used by another program.
3. The workbook you are trying to save has the same name as a currently open workbook.

The XXX file returns the first file in the directory, so it can get far enough to read the fully qualified file name. I would hope that rules out the first reason. I have verified that no open workbooks are open and no Excel processes are running in the background. I have set the Agent step to run as a proxy account that uses my account's credential, which solved a different Access is Denied 80070005 error.

This seems like the error is actually running into something else that I am not able to see (like a double hop). I am looking for suggests on how to further troubleshoot because I have not made any progress the last several hours reviewing other posts around similar issues.

Answer 1

Hi @Charlie Day ,

Please double check that you could open the file on the target server with the file path.

It should be an UNC path.

And then check-if-file-is-open-without-opening-file-in-script-task.

Please also try to run the package in 32bit mode in agent job to see if it will work.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Following these instructions in step 5 worked for me if I run from my local server. It solved the error I listed above.

https://docs.sentryone.com/help/task-factory-excel

Deploying the identical package with the same proxy will not run from the production server. This appears to be a permission issue to the share drive. If I point to a local drive it will run.