Tips to managing SharePoint SSRS Reports with Powershell

When you migrate one site collection with SharePoint SSRS Report solution from one environment to another, you will need to update the data source link to reflect the updated data source site URL. It’s extremely time consume to update the .rdl data source link though UI since out site has over 600 .rdl files.  After struggled few days, we are able to utilize the Powershell to automate the process updating the SSRS data source link based on this blog and instruction from Léon Bouquiet. The Powershell snippet is listed below.

$reportserver = "spsbxserver";

$reportServerUri = "http://$($reportserver)/_vti_bin/ReportServer/ReportService2010.asmx?wsdl";

$newDataSourcePath = "http://$($reportserver)/sites/ePlayBook1/SSRS/SPDS.rsds";

$reportFolderPath = "http://$($reportserver)/sites/ePlayBook1/";

#$newDataSourceName = "SPDS"; # No need to change data source name in our case

$ssrs = New-WebServiceProxy -uri $reportServerUri -UseDefaultCredential

$ssrs.Timeout=200000  # Set timeout in case large entries return to cause timeout

$reports = $ssrs.ListChildren($reportFolderPath, $true)

$reports | Where-Object {$_.TypeName -eq "Report"} | ForEach-Object {

       $reportPath = $_.path

       Write-Host "Report: " $reportPath 

       $dataSources = $ssrs.GetItemDataSources($reportPath)

       $dataSources | ForEach-Object {

        Write-Host "Report's existing DataSource Reference ($($_.Name)): $($_.Item.Reference)";

             $proxyNamespace = $_.GetType().Namespace

             $myDataSource = New-Object ("$proxyNamespace.DataSource")         

             $myDataSource.Item = New-Object ("$proxyNamespace.DataSourceReference")

             $myDataSource.Item.Reference = $newDataSourcePath

             #$myDataSource.Name = $newDataSourceName  # No need to change data source name

 

             $_.item = $myDataSource.Item 

             $ssrs.SetItemDataSources($reportPath, $_)

 

             Write-Host "Report's DataSource Reference ($($_.Name)): $($_.Item.Reference)";

       }

       Write-Host "-----------------------------------------------------"

}

We had few issues when we come up the final solution to manage the SSRS Reports with Powershell. Here are few tips that will help you to avoid the issue we encountered.

1. The first issue you might have is the exception for SSRS "Object reference not set to an instance of an object. ".  

The complete ULS log like this below.

01/19/2017 14:19:17.62         w3wp.exe (0x261C) 0x35CC    SQL Server Reporting Services               Report Server Catalog            00000                Unexpected             Throwing Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: , Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. ---> System.NullReferenceException: Object reference not set to an instance of an object.     at Microsoft.ReportingServices.SharePoint.Objects.RSSPImpUser.get_LoginName()     at Microsoft.ReportingServices.SharePoint.Utilities.CatalogItemUtilities.CreateCatalogItem(RSSPFile file)     at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPFileProperties(RSSPFile file)     at Microsoft.ReportingServices.SharePoint.Server.SharePointDBInterface.InternalFindObjects(ExternalItemPath wssUrl, CatalogItemList& children)     at Microsoft.ReportingServices.SharePoint.Server.SharePointDBInterface.FindObjectsRecursive(ExternalItemPath wssUrl, CatalogItemList& childList, Security secMgr, IPathTranslator pathTranslator, Boolean appendMyReports)     --- End of inner exception stack trace ---;         abafcc9d-e8f3-00e4-e462-39437ade3034

This issue is normally cause by the miss match SSRS add-in on SharePoint server and old SSRS add-in version as described in this KB article.

The solution is to upgrade the SSRS add-in on SharePoint server. In our case with SQL database version as 11.0.6567.0, we have to upgrade SSRS add-in to from version 11.0.3000.0 to SSRS 2012 SP3 with version 11.0.6020.0. Please refer my precious blog on the detailed procedure to upgrade.

2. The second issue you might have is the timeout issue. The error looks like this below.

Exception calling "ListChildren" with "2" argument(s): "The operation has timed out"

The solution is to increase the SSRS proxy timeout. Here is example we added in the Powershell as below.

$ssrs.Timeout=200000 

3. The third issue is the Powershell performance is extremely slow since most SSRS Powershell examples will loop through the whole SharePoint farm sites and lists recursively. You can imaging the time to go through all lists of the far. The Powershell script is like this below.

$reports = $ssrs.ListChildren("/", $true)

The solution is to pass the site collection URL to the ListChildren function. As a result, it will loop though only that one site collection. Here is the updated Powershell script.

$reports = $ssrs.ListChildren("http://mysharepointserver/sites/ePlayBook1/", $true)

If you only need to update the SSRS report on the top level site not recursively, you can also pass the send parameter as $false. There are few other blog you could refer on this topic.

New you could enjoy migrating the site collection from environment to another without have to change all the data source links manually.