SSRS - Excel data source error when report deployed to localhost report server

Question

I need help with an SSRS report deployed to localhost which uses ODBC data source to retrieve data from excel file. When the same report is previewed through BIDS 2008, it displays the results as expected. But after deploying the report to the Report Server on the same machine (localhost) where BIDS is running it gives the below error.

ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'dsInvoice'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I have tried shared data source as well as custom data source but both didn't work. When I looked up for this error it mentioned that I need to use 32-bit ODBC Administrator tool under %windir%\SysWOW64\odbcad32.exe and that's what I'm using but that also didn't work. I'm an Administrator on the local machine.

Below is some information about the machine:

• Windows 7 64 bit
• SQL Server 2008 R2
• Office 2010 32 bit

Steps to reproduce the error:

1. Create a Microsoft Excel Driver User DSN - Test_Excel_Driver - under %windir%\SysWOW64\odbcad32.exe

2. Create an SSRS report using BIDS 2008 and select datasource as an ODBC Embedded Connection with following connection string - Dsn=Test_Excel_Driver;dbq=C:...\Test Excel.xlsx;defaultdir=C:...\;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5

3. Preview the report in BIDS - Works fine !

4. Deploy the report to localhost SSRS Report Server with full permissions. Click on the report in report manager and get the above error.

Answer 1

First off using OLEDB instead of ODBC will be easier to get going, the connection string for OLEDB will look like:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";

Then in SSRS/BIDS when you click "Test Connection" in the DataSource screen you may see this error:

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

The documentation states MS Access drivers (including the OLEDB driver) only work under the x86 platform and is incompatible under the x64 or AnyCPU platform. However this appears to be untrue. Ref: https://stackoverflow.com/a/32760211/495455

First download the installer making sure you tick the _64.exe version: http://www.microsoft.com/en-us/download/details.aspx?id=13255

1. Extract the AccessDatabaseEngine_x64.exe to a folder to get the AceRedist.msi and Data.cab files.
2. Open command prompt in Admin mode
3. cd to the folder you extracted the download and execute the MSI with the passive argument:

AceRedist.msi /passive

After these steps I managed to run the application (and also SSRS without BIDS) after building in x64 or AnyCPU build configuration. This solves issue.