Connection String for ODBC on report server?

Question

I want to create a datasource on report server from an excel. I put my excel to report server. Than i wrote a connection string, but it did not work. What is wrong? "Data Source=http://xxxxx.com;Initial Catalog=MyExcelName"

Answer 1

Your report server probably has wizards to help you click through setting up your string. To read from an excel file you would use OLE DB, not ODBC. The end result of the wizard will be something like:

Provider=Microsoft.ACE.OLEDB.12.0;

Data Source=C:\ReportData\MyExcel.xlsx;

Extended Properties="Excel 12.0;HDR=YES"