How to execute OPENROWSET from SSRS

Question

Trying to create a Dataset in SSRS that executes a stored procedure that imports data from an Excel file. The sproc transforms the imported data and outputs the reformatted data for the SSRS report. (Note: I do NOT want to create a data source directly to the Excel file).

I'm using the following stored procedure to test the import. It executes successfully when run from SSMS...

CREATE PROCEDURE dbo.udp_ImportTest 
 AS 
 SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0' 
 ,'Excel 8.0; Database=\\SCDATA\Import\AUM_Extract.xls'
 ,'SELECT * FROM [Sheet1$]') 
 GO

I created a dataset in SSRS that executes the above sproc, which throws this error:

An error has occurred during report processing.
(rsProcessingAborted) Query execution failed for dataset 'DataSet1'.
(rsErrorExecutingCommand) The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

The SSRS account has full-control permission on the folder where the Excel file resides.

Why won't this work from SSRS?

Answer 1

Something Tells me that you are trying to populate a table using the file that you are doing OPENROWSET with. This is a case for SSIS not SSRS, so please try that instead.

Anyway, try givign SQL Server full permission on the folder where the ACE.OLEDB.12.0 is located. Here is a good blog post to find out how: https://visakhm.blogspot.ae/2013/12/how-to-solve-microsoftaceoledb120-error.html