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.
