SSRS: How to fix rsInvalidDataSourceReference

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.

SSRS Configuration Manager - Database

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 :)).

Leave a Comment