Error rsInvalidDataSourceReference after Moving RDL Files to Another SSRS Server

Moving report files from one SSRS server to another is easy if it is just one or two. You can just do it through the browser.

When it is more than a few things are not that simple.

To download, and upload the files than our article How To Deploy a Folder of RDL files Using RS.exe will help to get things started.

Even if you get the datasources named the same and everything appears to line up I have noticed that the first time you do so the datasources may not link properly.

Image: Source

This is due to a GUID that is stored behind the scenes.

Disclaimer. Microsoft doesn’t support the updating of their system tables so I would never recommend doing this in a production environment.

You can use this method to speed up the refreshing of your dev/test environments if you are prepared to take the risk.

rsInvalidDataSourceReference is the error that you might get if you are having this issue.

Identify the problem records.

 
-- Shared Data Source references with broken links 
SELECT ItemPath = c.[Path], ItemName = c.Name, DatasourceRefrenceName=ds.Name, ds.Link 
FROM [Catalog] AS c 
INNER JOIN DataSource AS ds ON ds.ItemID = c.ItemID 
WHERE ds.Link IS NULL AND ds.Extension IS NULL 
------------------------

You will see that you need to update the GUID and to find out what GUID to use, find a report that is working properly and has the same datasource.

That is the GUID you need. You might want to put additional filters into the query below depending on what you are trying to fix.

 

BEGIN TRAN 
UPDATE ds SET [Flags] = [Flags] | 2, link = 'xxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx' - Put your GUID in here 
FROM [Catalog] AS c INNER JOIN DataSource AS ds ON ds.ItemID = c.ItemID 
WHERE ds.Name = 'DEV' --Replace with your name 
and ds.link is null 
COMMIT 
---- Do not run in production

You probably are wondering why we need to update the flags field as well.

The reason is that this particular field appears to advise SSRS as to whether or not the report has an issue.

If you have 50 or 100 reports to move, using the above queries can save a lot of time vs doing it manually.

References
When someone deletes a shared data source in SSRS
SSRS: How to fix rsInvalidDataSourceReference

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply