SSRS - Excel data source error when deploying a report to localhost report server

Question

I need help with an SSRS report deployed to localhost that uses an ODBC data source to retrieve data from an excel file. When the same report is viewed through BIDS 2008, it displays the results as expected. But after deploying the report to the report server on the same computer (localhost) where BIDS is running, it gives the following error.

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

I tried using a common data source as well as a custom data source, but both of them did not work. When I looked for this error, he mentioned that I need to use the 32-bit ODBC admin tool in% windir% \ SysWOW64 \ odbcad32.exe , and that what I use, but which also doesn’t work. I am an administrator on a 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:

• Create a Microsoft Excel user driver - Test_Excel_Driver - under % windir% \ SysWOW64 \ odbcad32.exe

• Create an SSRS report using BIDS 2008 and select the data source as an embedded ODBC connection with the following connection string: Dsn = Test_Excel_Driver; dbq = C: ... \ Test Excel.xlsx; defaultdir = C: ... \; driverid = 1046; fil = excel 12.0; maxbuffersize = 2048; pagetimeout = 5

• Report preview in BIDS - works great!

• Deploy the report to the SSRS Report Server on the local host with full permissions. Click the report in the report manager and get the above error.

Answer 1

At first, using OLEDB instead of ODBC will be easier, the connection string for OLEDB will look like this:

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” on the DataSource screen, you can see this error:

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

The documentation states that MS Access drivers (including the OLEDB driver) only work under the x86 platform and are not compatible with the x64 or AnyCPU platform. However, this seems wrong. Link: fooobar.com/questions/27530 / ...

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

• Extract AccessDatabaseEngine_x64.exe into the folder to get the AceRedist.msi and Data.cab files.
• Open command prompt in admin mode
• cd to the folder where you extracted the download and execute MSI with a passive argument:

AceRedist.msi /passive

After these steps, I managed to launch the application (as well as SSRS without BIDS) after creating the x64 or AnyCPU build in the configuration. This solves the problem.

Answer 2

Are you creating a DSN on the Custom DSN tab? If so, try creating it under a DSN File or System DSN and try again?