Tuesday, March 23, 2010

Using powershell to mass update SSRS Reports

I have often needed this and decided to finally sit down and write it.  It basically, takes a set of reports and changes the data source to something else.  In my case, I had a folder of reports that were pointing to one datasource and I wanted to point them all to another.

#update report server data sources with powershell
$computer = "repcomp"
$uri = "http://$($computer)/ReportServer/ReportService.asmx?WSDL"

$reporting = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportingWebService"
#I wanted to change the datasource for all reports in this folder, ymmv
$colReports = $reporting.listchildren("/Test Audit Reports", $true) | where {$_.Type -eq "Report"}

foreach ($rep in $colReports)  {
      #I also knew each of my reports only had one datasource, you may need more logic here.
      $datasource = $reporting.GetReportDataSources($rep.path)[0]

      $datasource.Item.Reference = $datasource.Item.Reference.ToString().Replace("/Audit Reports", "/Test Audit Reports")
      Write-Host "Updating: $($rep.Name) to $($datasource.item.reference)"
      $reporting.SetReportDataSources($rep.Path, $datasource)

No comments:

Post a Comment