I was analyzing my blog stats today and noticed that really a lot of people land at my old blog post about fighting with rsInvalidDataSourceReference in System Center Operations Manager environments. As far as I understand, that is not exactly what most of the SSRS guys are looking for.
I decided to fix this, so please find below my explanation of the nature of rsInvalidDataSourceReference.
First things first, what is the Data Source, what is Reference and why is it Invalid?
What is DataSource?
Data Source is an item which is used to store connection strings and credentials. There are two types of Data Sources in SQL Server Reporting Services: Embedded and Shared. The first one can be used only within a report which defined it, whilst the last one can be used by multiple reports.
What Is Reference?
Reference is used to refer to the Shared Data Source (sorry for tautology). When you need to reuse the Shared Data Source in one of your reports, you just select it by name in Business Intelligence Development Studio (BIDS or SQL Server Data Tools since SQL 2012). And that’s literally what happens – you choose it by name. Though you can see come GUIDs inside of .rds and .rdl files, they are not used for binding.
Why is it invalid?
Now, the most interesting part – how could Data Source reference be invalid? Easy. The truth is that Data Source has no unique property which could be defined by developer or SSRS Administrator. Seriously. I’m not kidding. By the way, report does not have that either. Only combination of Path and Name makes Report or Data Source unique within SSRS deployment. Thus, when you deploy or re-deploy either Report or Shared Data Source, you have to establish or re-establish the reference. Here is the list of some cases when you should take care of that:
- You’re deploying both Shared Data Source and Report;
- You’re deleting and re-creating a Shared Data Source;
- You’re deleting and re-creating Report (which uses a Shared Data Source).
These actions obviously break the link between two SSRS Items.
Okay, but why we have everything working when we deploy our project using BIDS (or Data Tools)?
That is because BIDS implements the right deployment process. It not only uploads items to the SSRS instance but also sets the correct reference for you.
Who is affected?
The easiest way to find all affected SSRS Reports is to query SSRS catalog database. Its default name is ReportServer, but you can use Reporting Services Configuration Manager to find the one you use.
Now, all we need is the T-SQL query. Here it goes:
-- Shared Data Source references with broken links SELECT ItemPath = c.[Path], ItemName = c.Name, DatasourceRefrenceName=ds.Name FROM [Catalog] AS c INNER JOIN DataSource AS ds ON ds.ItemID = c.ItemID WHERE ds.Link IS NULL AND ds.Extension IS NULL
How to fix?
There is no hidden knowledge here. All we need is to restore the link between Report and Shared Data Source. Instructions are available at TechNet.
If you have plenty of such issues, you may try to update the [Link] field of [dbo].[DataSource] table directly. Be careful here – you may have several Shared Data Sources with the same name (but in different folders), so make sure that you’re linking right things. Of course, don’t forget to make a backup of your SSRS database. Also, do not point to me if something goes wrong – I have never done that on a production environment.
Some more candies
Finally, here are some extra scripts that can be useful for you to keep an eye on your SSRS assets.
--All Shared Datasources SELECT ItemPath = c.[Path], ItemName = c.Name, ds.Extension FROM [Catalog] AS c INNER JOIN DataSource AS ds ON ds.ItemID = c.ItemID WHERE c.[Type]=5
-- Who uses shared data source SELECT DataSourcePath=cds.[Path], DataSourceName=cds.Name, DataSourceExtension=sds.Extension, ItemPath = c.[Path], ItemName = c.Name, DatasourceRefrenceName=ds.Name FROM [Catalog] AS c INNER JOIN DataSource AS ds ON ds.ItemID = c.ItemID INNER JOIN [Catalog] AS cds ON cds.ItemID=ds.Link AND cds.[Type]=5 INNER JOIN DataSource AS sds ON sds.ItemID = cds.ItemID
And some official documentation on the matter:
I hope this explanation is what you were looking for and do not hesitate to post a comment if you have something to add (or just say something :)).