I have been learning a lot of SSRS and SQL server recently and have wondered how to apply the awesome toolset of SSRS to some other problems I have found.
From most of the resources I have found it is usually quoted that the only way to use excel as a datasource for SSRS is by adding the excel document as a ODBC, however in lots of environments that will not be possible due to security settings.
In the back of my mind I was sure that I had seen excel used with OLE DB data source before.
Finally I found an example of it working here;
Essentially you use the following connection string
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
and all works as hoped
Success!
The only other point to make is that you need to reference the sheet name with a $ suffix,
Connection strings,
https://www.connectionstrings.com/excel-2007/
Stack exchange question about the same problem:
http://stackoverflow.com/questions/24499299/connecting-excel-2013-to-ssrs-2012-to-use-as-a-datasource
The only other point to make is that you need to reference the sheet name with a $ suffix,
SELECT
F1 AS rowNum
,F2 AS theValue
FROM
[Data3$]
More resources:
I wish I found these earlier!
https://www.connectionstrings.com/excel-2007/
Stack exchange question about the same problem:
http://stackoverflow.com/questions/24499299/connecting-excel-2013-to-ssrs-2012-to-use-as-a-datasource
