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
administration 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.
We have created two PowerShell scripts which will aid in this process:
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
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.
If you have any questions please leave a comment.