Posted on:
Categories: SharePoint;SQL
Description:
Symptoms For testing purposes we recently had to restore a production site to one of our staging environments. This site contains several SSRS report libraries. After restoring the production site to the staging environment we noticed that several reports produced the following error when opened At first this appeared to be a strange error because the data source was clearly set on for the RDL file. So, why was SharePoint complaining that the shared data source was invalid? One thing that I examined while trying to determine the cause of this error was the report server database. This is the database that is configured in your SSRS server configuration settings on your SSRS server, and it contains a great deal of metadata about your SSRS reports, datasets and data sources. By running the following query against the report server database I was able to see that the data source link for the RDL file was definitely correct SELECT DSID ,DS.ItemID ,Link ,C.Name AS ItemName ,DS.Name AS DSName FROM DataSource DS JOIN Catalog C ON DS.ItemID = C.ItemID In the first screenshot you can see that the 'ItemID' for the GWAOLAP.rsds data source is 75E84723-B162-45AE-B093-550394CCE3EF. This matches the 'Link' value for the RDL file, as seen in the second screenshot ('Link' refers to the data source reference in this case). So, everything appears to be in order here. The problem must lie elsewhere. Solution The solution turned out to be quite simple, even if it wasn't obvious from the error message given above. The RDL file was configured with several datasets. Each of these datasets has its own data source reference, and each of these references were set to null after a migration from the production environment. Each dataset needs to be configured with a data source reference. Once the data source links were configured for each of the datasets the error went away and the report loaded successfully. In summary, make sure that you set the data source references for your data sets in addition to the data source references for your RDL files.