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.

Verulamium

Can anyone help me with this issue? I really need to find a solution and I have searched quite a bit for it without any luck. – Verulamium

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://mcmap.net/q/64379/-39-microsoft-ace-oledb-12-0-39-provider-is-not-registered-on-the-local-machine

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.

Percaline

Hi Jeremy, the solution provided by you resolved my issue. I was able to run the report off SSRS by using OLEDB connection rather than ODBC. Thanks ! – Verulamium

Answer 2

Have you created the DSN under User DSN tab? If so can you try creating it under File DSN or System DSN and try again?

Chatelaine

Hi Sam, yes I created it under User DSN and it didn't work. It failed for File DSN as well.When I created it under System DSN, I got a different error:**ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application** – Verulamium

In that case can you create the DSN in 64 bit (C:\Windows\System32\odbcad32.exe) under System DSN and give it a try? – Chatelaine