Issue after deploying SSRS report that pulls data from an Excel data source to the Report Server

Question

I am trying to deploy an SSRS report that is using an Excel data source. This report shows up fine on BIDS when I preview it.

I then tried to deploy it to the Report Server and that worked fine except that when I click on the report I get the following error:

An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'Excel_64'. (rsErrorOpeningConnection) ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection

I have looked online and tried a bunch of different solutions ranging from sharing the workbook, trying to input Windows Credentials in the report server in the "Credentials stored securely in the report server" field but that also gave me the same error.

I would really appreciate any help in resolving this.

Thank you.

Answer 1

Did you try removing the Trusted_Connection attribute from the connection string?

Answer 2

Does that server have all necessary components installed to retrieve data from a Excel sheet? I seem to remember that in older versions you needed Data Access Componets(DACS) or Excel itself installed on the server from which the Excelfile is read.

SSRS must Excel be installed on Reporting server to use Excel as a Data SOURCE?

But I think you would be better of with the solution proposed in the link below. It suggests importing the data into a SQL table using a scheduled SSIS package.

SSRS: Error while querying data from an Excel file (through ODBC)