Excel File cannot open

Question

So we have a pretty simple SSIS Data Pump from Excel into a SQL Server Table. We went to test it and we stopped it before we thought it started in order to check the OLE DB Source Connection.

Now we are getting the dreaded error:

***Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "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."

Error at Data Flow Task - Data Pump [Excel Source (Spreadsheet Name) [44]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method to call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. ***

And when I try to actually open the Network Excel file spreadsheet within Excel, we similarly get the error message:

(Spreadsheet Name).xlsx is locked for editing by 'another user'.
Open 'Read-Only' or click 'Notify' to open read-only and receive notification when the document is no longer in use

So it sure looks to us as though when we started the SSIS Package to test it, SSIS attempted to open and read the Excel spreadsheet and then we stopped it mid-stream and it looks as though SSIS has maintained its lock on this Excel Spreadsheet. We know no one else is looking at or accessing this Excel spreadsheet so we have to assume it has a SSIS lock on it and will not release it.

We kind of vaguely remember having to go to the registry editor or somewhere else to release this lock but we cannot remember that procedure. And we will be sure to document this in our Knowledgebase at this point for future reference.

Can anyone help us out here to find and release this SSIS lock on the Excel spreadsheet?

Thanks for your review and am hopeful for a resolution and Thanks in advance for your help.

Answer 1

Historically there was a lock file created next to the original file in the file system. But I don't think it does that on newer Excel versions.

In my experience, when this happens, the Excel process (which is loaded by COM) is hanging around. Out of proc servers that aren't properly closed think they are still in use and won't go away. Look at the running processes and terminate any Excel processes. This should release the lock.

Answer 2

Yep...this is how we fixed it...

• Start...Task Manager
• Under the Processes Tab and Background processes locate SSIS Debug Host (32 bit) and End task

Answer 3

Hi @Bobby P ,

Sorry if this is a silly suggestion, what about copy the Excel file to a different location under a new unique name and then run the SSIS package.

Use file system task to copy and rename the file and then run the other task you want.

If the excel is locked, you may use the back up file.

Regards，

Zoe Hui

---

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