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.


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!

Marcos Freccia
Data Platform MVP


About Marcos Freccia

Sr. Database Architect focused on relational databases as well as no relational databases working currently at Amazon Web Services (AWS)

Posted on October 15, 2018, in Administração, Powershell, Reporting Services, Scripts and tagged , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: