I am trying to render a simple chart using SSRS. The data is in an Excel sheet. I have set up an user DSN and created a data source in SSRS using ODBC.
I am able to query the excel in "Data" tab. However while trying to preview, I get the following error:
error [hy000] [microsoft] [odbc excel driver] the connection for viewing your linked Microsoft Excel worksheet was lost.
Anyone knows why this is happening and how this can be solved?
Seems to be a fleeting error. I deleted the dataset, created a new one and re-ran again. It worked.
I would consider creating a SQL Sever Integration Services package to import the data from Excel to a SQL database and using the database as the data source for the report. The SSIS package could be scheduled to periodically refresh the data. Using a file based data source like Excel is vulnerable to people modifying the structure of the spreadsheet and moving, deleting or locking the file.