SSRS 2017 The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Brandt Smith 96 Reputation points
2021-03-31T13:47:17.48+00:00

I've seen numerous articles about this but nothing seems to work on my 64bit SQL 2017 server with SSRS installed. I am using Report Builder on that machine and can create the Data Source but the query returns that message. The Excel file is local xlsx file. It is a 32 bit excel file exported from a SP2010 site list. Using ODBC and DSN seemed not to work , so I am trying to use OLEDB. This should be easy!

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
3,067 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Michael Taylor 60,346 Reputation points
    2021-03-31T14:35:17.35+00:00

    There is no such thing as a 32-bit Excel file. Office files don't have bitness. You probably meant you created the Excel file using the 32-bit version of Office. But that doesn't matter for opening the Office document.

    Data sources are either x86 or x64 and you use 2 different UIs to set them up. If you simply typed "Data Source" into Windows search then it likely brought up the 32-bit version. You need to use the x64 version to set up the data source if you are using SSRS to connect to it. However the driver has to be installed first. Open the x64 version of data sources and ensure the ACE driver shows up. If it doesn't then you need to install the x64 version of the driver first.

    But here's the thing, SSRS is just the server side. If you are building a report then you'll be using a separate tool, likely the VS Shell which is 32-bit. Therefore when you are designing the report you'll be using the 32-bit version of the driver. However when you deploy the report to your SSRS server you'll need to configure the data source in the SSRS Report Manager UI to use the x64 driver. This basically just means opening the data source in the UI and ensuring it properly connects to the source. If both x86 and x64 versions of the driver are installed it should just work.

    Please clarify when you are getting the error: When you go into preview mode in the designer, when you attempt to deploy and run the report on the server, etc?

    What designer are you using to test with (VS Shell's preview, SSRS Report Manager, etc)?

    Have you confirmed in the Data Sources of Windows for both x86 and x64 that the driver is installed?

    If you can see the data source while designing/previewing but not when trying to run from the server can you confirm if you can see the data source on the SSRS server?

    0 comments No comments

  2. Brandt Smith 96 Reputation points
    2021-03-31T14:59:30.413+00:00

    OK...thanks...I am using the Report Builder on the 64 bit sql server to design the report. I cannot get the report to run using Report Builder...I have try installing the 32 & 64 bit versions of the AccessDatabaseEngine .
    So I'd like to be able to have users upload an Excel file to a shared folder (on the SQL Server machine) and then run the report from SSRS...OLEDB?

    When I use the OLEDB string (Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ExcelDataSSRS\AttendeeList.xlsx;
    Extended Properties="Excel 12.0 Xml;HDR=YES";) it connects OK. But when I set up the query I get an unhandled exception...there is so much conflicting steps to solve that by installing AccessDatabaseEngine(s)...I have installed and uninstalled them multiple times and either hit that exception or an architecture mismatch.


  3. Brandt Smith 96 Reputation points
    2021-03-31T15:18:36.41+00:00

    OK...so I just devd it on my machine and created a matching folder & file on my machine...and it ran...then uploaded to SSRS and it ran! SO - dont develop on the SSRS machine is only thing I can come up with! I didnt even have to fiddle with the data source...it is embedded...PITA for sure figuring this out! Your post helped though got me thinking about the separation of data sources


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.