Connect all SSRS Reports in SharePoint library to DataSource using Powershell

Question

We are migrating MOSS to SP2010 and have a library with hundreds of SSRS reports that use a Shared DataSource (in SharePoint Integrated mode.) Since the ReportServer database location has also changed, we lose all of the connection data between the reports and their Shared DataSource. Rather than going through each report in the UI to "Manage Data Sources," I would like to update all of the reports via Powershell.

I've made it as far as being able to isolate the report files in a foreach loop, but am not sure how to assign the datasource property.

Here is what I have so far:

$web = Get-SPWeb -Identity "http://contoso.com/" 
$list = $web.GetList("http://contoso.com/ReportLibrary") 

$folderUrl=($list.RootFolder.url) 
$folder = $web.GetFolder($folderUrl)

foreach ($file in $folder.Files) { 
    If ($file.name.EndsWith(".rdl"))  {
        write-host $file.Name
    }
}

I guess I could write a console app to do this, but I'm trying to learn how to make my life easier with Powershell. Thanks for any help offered.

On a side note, if I backup and restore the ReportServer database in the new location, would that solve this issue?

Answer 1

You may use powershell to set the new DataSource. As noted from this blog post, you can use Set-SSRSDataSourceInfo.

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.