SSRS : Bulk Update Custom Data Source

Question

Hello, this question is somehow related to https://social.msdn.microsoft.com/Forums/en-US/445403e4-8cc8-47ae-ac09-d20db6a9c526/ssrs-migration-issue?forum=sharepointgeneral, where unfortunately I do not see the direct answer.

The issue is after upgrading our software version, hundreds of ad-hoc reports in SSRS has its custom data source not set. Manually, a few reports has been edited and it works when correcting the custom data source link. Likely there was an issue with the upgrade but the question is there a way to bulk update custom data source link for hundreds of reports?

Please see the sample screenshot of the issue below. Apologies if its in Japanese but I hope it is clear where the error and it just says "It cannot find the Data Source and if it had been moved, please select".

For reference, here is the equivalent screenshot in English, although this one do not really have an issue.

Answer 1

Hi @Larimore Suanque ,

What you said is a good way to browse to the data source that exists on the server and reconnect to the data source. Actually this is how I solve it on my machine before, but last time I only have c.a. 20 test reports.

Long time ago , when I upgraded one report server I encountered similar issue. Also when we tried to migrate the reports to other server (using MSbuild, if I recall correctly) we also meet similar issue. It seems like an automation problem within the upgrade or migration process.

You could try solve this problem by executing a SQL script see if it works for you. This is a method described in this blog but for SCSM environment. Maybe it could help.

USE [ReportServer]  
GO  
  
UPDATE [dbo].[DataSource]  
SET [Link] = (SELECT TOP 1 [ItemID] FROM [dbo].[Catalog] where name = [dbo].[DataSource].[Name])  
WHERE name is not null and link is null  

Or, did you have the Report Server database/encryption key backup before this upgrading ? If you do, you could try recover the database backup/ encryption key directly on the new server. This may also work around this issue.

Hope this could work.

Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you. We will test with the SQL you mentioned and update here the results.

Answer 3

Hi @Larimore Suanque ，

Has your issue been resolved?

If you have any question, please feel free to let me know.

Best Regards，
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.