Search code examples
exceldynamicssisssis-connection-manager

SSIS Excel Connection Manager using Expression fail : "Cannot update. Database or object is read only"

I am struggling on this error for a long time now.

Here is the configuration of my project :

  • a server SERV_SSIS_CATALOG where SSIS packages are deployed
  • a server SERV_FILE (another) with files accessible by the previous server (and by my user also)
  • an SSIS package with an excel connection manager.

Objective : Create a dynamic excel connection manager using project parameters, to ingest the data from a SQL request to an Excel File.

With a static value to a file on SERV_FILE, my package works perfectly fine and loads this excel file. It does work whether I execute the package locally or on the SERV_SSIS_CATALOG.

When I tried to create an expression for the ExcelFilePath, it didn't work at all, no matter what I did. I couldn't even Preview the file in the SSIS Task Excel Source.

When I tried to create an expression for the ConnectionString I did have a bit of success, when I added IMEX=1. I could preview the file in the SSIS Task Excel Source. I tried to add ReadOnly=0 in the Extended Properties, it didn't work.

But the package keep failing, both locally or on the SERV_SSIS_CATALOG, with this error :

Load Excel Base File:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E09.
An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80040E09 Description: "Cannot update. Database or object is read-only.".

I checked permission on the Excel File on the SERV_FILE, I do have everything on Full Control.

I don't know what to try next, or if there is another way to do the ingestion of a SQL Request to an Excel File.

Edit : Something curious I noticed, is that the ConnectionString is not visible in the package ConnectionManagers parameter on the SERV_SSIS_CATALOG if I do use an Expression (otherwise it is totally visible). But I don't know what to think about that, because the package seems to try to access to the file and is refused due to Read-Only property.

Solution

  • I finally found the solution.

    In an Excel Connection Manager, if the ConnectionString expression is parameterized, then the ExcelFilePath needs to be parameterized too.

    Example :

    ConnectionString : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[$Project::excel_file_path] + ";Extended Properties=\"EXCEL 12.0 XML;HDR=YES\";"

    ExcelFilePath : @[$Project::excel_file_path]

    Note the use of backslash in the expression of the connection string, required for the double quote.

    The first and last part of the connection string can also be parameterized if you have many Excel Connection Managers.