How to resolve SSIS error "Excel File is already opened exclusively by another user"?

Question

Problem:

I have an SSIS package that I created in Visual Studio 2015 for SQL Server 2012. The package loads a tab delimited file and some records from CRM, does some processing, and logs errors in an excel file on a shared drive. When I run the package from within Visual Studio, the package executes correctly without error. However, when I deployed the package to the server and tried to run the package from the server as a job, I get the error:

The Microsoft Access database engine cannot open or write to the file. It is already opened exclusively by another user, or you need permission to view and write its data.

Attempted solutions:

• I have checked the folder permission for where the excel file is located and changed them to allow everyone to have full access to the folder. I did this to the share as well as to the folder and individual file

• I tried running the package directly and as a part of a SQL Server job

• I am able to open the file independent of the application so it doesn't appear to be locked by another user

Answer 1

After searching there are many ways that you can try to resolve this issue. you can make a try:

1. Verify that the file isn’t actual open (check task manager for suspicious EXCEL.EXE processes)

2. Verify that there are no other SSIS packages (or other background processes) that might be opening this file at the same time

3. Create a custom task that waits until the (excel)file is unlocked

References

• https://www.mattmasson.com/2013/04/error-the-microsoft-access-database-engine-cannot-open-or-write-to-the-file/
• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a72bbd11-1ea4-4645-a2d9-b0afaf206005/ssis-error-with-excel-data-source?forum=sqlintegrationservices
• http://microsoft-ssis.blogspot.nl/2012/01/custom-ssis-component-file-in-use-task.html