Business Case
SQL Server Reporting Services(SSRS) is becoming an integral component of any business today. Many of our clients have sites with hundreds of SSRS Reports. SharePoint 2010 with SSRS in Integrated mode simplifies the configuration and management of SSRS Reports by allowing many of the administrative tasks to be configured through SharePoint. However, there is one large caveat.
When using Shared Data Sources setting the Report Data Source URLs is a manual and tedious procedure which has to be configured one report at a time. One can imagine that a site with a hundred reports can take a considerable amount of time.
Furthermore, there are a number of scenarios where the Data Source information needs to be reconfigured:
When a SharePoint Cumulative Update is deployed
We have noticed in the past that Data Sources are invalidated after a CU update and have to be reconfigured.
SSRS rebuild
There a number of cases where the SSRS server may need to be rebuilt, ie losing the encryption key.
Initial Deployment
The Data Source URLs have to be set during the initial deployment of the reports.
Solution
We have created two PowerShell scripts which will aid in this process:
Get-SSRSDataSourceInfo will iterate through all the reports in a Document Library and display the Data Source URLs for every Data Source. Simply run the function with a Document Library URL as the parameter.
http://gallery.technet.microsoft.com/scriptcenter/Get-SSRS-Data-Source-19360302
Set-SSRSDataSourceInfo will set the Data Source URL for every report in a Document Library. Simply run the function with a Document Library URL, Data Source Name and Data Source URL parameters.
http://gallery.technet.microsoft.com/scriptcenter/Set-SSRS-Data-Source-3b074747
If you have any questions please leave a comment.
Enjoy!
Written By:
softlanding
Softlanding is a long-established IT services provider of transformation, professional services and managed IT services that helps organizations boost innovation and drive business value. We are a multi-award-winning Microsoft Gold Partner with 13 Gold Competencies and we use our experience and expertise to be a trusted advisor to our clients. Headquartered in Vancouver, BC, we have staff and offices in Toronto, Montreal and Calgary to serve clients across Canada.
More By This Author
