Linking Data Sources to SSRS Reports with PowerShell

Imagine the following scenario: You just migrated a bunch of SSRS Reports to a new Reporting Services instance, which has a different folder structure. In this perspective, one of the things that get lost along the way is the linking between Data Sources and Reports.

If you are in the PowerShell world like me you know very well this repository in the Microsoft organization on Github: ReportingServicesTools.

Reporting Services Tools is a module that interacts with Microsoft SQL Server Reporting Services, making it easier several tasks like importing and exporting reports, creating folders, subscriptions, data sources and so on. The module is maintained by Microsoft along with some know PowerShell folks from the community like my friend Claudio Silva(@claudioessilva) from the dbatools team.

Anyway, coming back to that I what wanted to show, sometimes linking data sources back to the Reports might be really painful, but having PowerShell in our favor makes things a lot easier. The script that I will show you below looks to a certain SSRS Path in your Reporting Server database to retrieve the reports that needs to be updated.

One important note about this script is that even if you have the report already linked to a data source, it will still show up in the list to be updated.

source: https://gist.github.com/marcosfreccia/a01d5e02c76ed2f325b08ebef35407e2

As you can also see in the script, in case one of the reports fails to get updated, I am returning in the catch statement the affected one.

Another question that you may have is: What about if I have different data sources per report like Report001 –> DataSource001 | Report002 –> DataSource002

If this is your case, in the select statement you need to return the results only for the concerning data source.

I am pretty sure that there might be better approaches to this, and if you are doing something different, I would love to see your solution! However, this is working pretty well for me and it has been helping me for quite some time!

Cheers!
Marcos Freccia
Data Platform MVP

Advertisement