Can anyone tell me whether it is possible to update SSRS shared data sources en masse or will I have to update each report individually?

I googled updating data sources via Powershell and came across script on following website:

https://ask.sqlservercentral.com/questions/86369/change-datasource-of-ssrs-report-with-powershell.html

However, when tweaked and ran it updated item (data source path) but not name. In trying to work out why this was the case I came across info on following website:

https://msdn.microsoft.com/en-US/library/microsoft.reportingservices.interfaces.reportoperation(v=sql.105).aspx

which stated "A user may update or modify single data sources associated with a given report. This does not include shared data source."

Someone else suggested updating XML in the directory which contains all the reports by replacing the datasource node with the new one but this was done in BIDS/Data Tools whereas all my reports have been developed in Report Builder. It was done this way before I arrived and I've never got round to moving them to BIDS/Data Tools because:

1: There's hundreds of them

2: On report manager there is a hierarchical folder structure which Microsoft, for reasons unknown, won't allow us to replicate in Data Tools

Does this mean shared data sources cannot be updated en masse? Am I stuck with updating each report individually? There are nearly 700 reports need cha

Hi Sugsy,

In your scenario, if those 700 reports are referenced shared data sources, you can list all shared data sources used by those 700 reports. Then we can use the GetDataSourceContents method to get the contents of the Data Source and then use the SetDataSourceContents to reset the contents of the Data Source. After updating contents of those shared data sources, those reports can work fine.

Reference:
Updating Data Source through Script - ReportServer Database
Extract metadata from report server database

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu

Hi Qiuyun, Sorry for not replying earlier - I've been off on leave for last couple of weeks. Your reply sounds straightforward enough so I'll give it a go when I get back to work Monday and if I have any further questions I'll give you a shout. Thanks for the help though - much appreciated.