CODEWITHSUNDEEP
excelvisual-studiossissql-server-data-toolsunc
ovidiufelixb
ovidiufelixb

Reputation: 45

Excel file on network share - It is already opened exclusively by another user, or you need permission to view and write its data

I am developing an SSIS package that uses a data flow task that has an Excel source. The provider for the connection is Microsoft.ACE.OLEDB.12.0.

If I point the connection manager to a local file on my computer and run the package in Visual Studio, it completes successfully.

However, if I point the connection manager to a UNC network share on my local domain, and run the package from Visual Studio, it fails with the following messages:

Error: 0xC0202009 at MyPackage, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. 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: 0xC020801C at Data Flow Task, Excel Source [2]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

More info:

Upvotes: 0

Views: 2510

Answers (1)

ovidiufelixb
ovidiufelixb

Reputation: 45

Solved it by using a variable for the file path and then using that variable as an expression for the ConnectionString property of the connection. I also doubled the backslashes in the variable that contains the pash, so instead of \computer\share\ I used \\computer\share\.

My guess is that the slashes solved the problem, but I've yet to test and find out for sure.

Upvotes: 1

Related Questions