SSRS SharePoint Mode Migration Scripting

The IT Leader's Guide to Multicloud Readiness

This guide provides practical key insights and important factors to consider to make informed decisions in your multicloud journey.

Download the Guide

I am currently at a client who is attempting to migrate Reporting Services objects from SharePoint 2007 to SharePoint 2013.  There are two paths they can choose for the migration: Copy the files over or do a database-attach upgrade to 2010 then 2013.  Either path they choose they will still have to set the reports shared data source(s) to the new location.  There is more than one way to skin this cat…  I started with a vb script using RS.exe but found it difficult to debug.  So instead I threw the code into a Visual Studio project and added the web reference to the reporting services API, http://<Server Name>/_vti_bin/ReportServer/ReportService2010.asmx.  After I tested my code I tossed it into a script task in an SSIS package.  This allowed me to easily loop through the reports on the old report server and then set the data source on the new server accordingly.

Here is the content of the Execute SQL Task:

SELECT   
     C.Path AS ReportPath,
     C.Name AS ReportName, 
     DS.Name AS DataSourceName,
     C2.Path AS DataSourcePath
FROM DataSource AS DS       
     INNER JOIN Catalog AS C ON DS.ItemID = C.ItemID
          AND DS.Link IN (SELECT ItemID FROM Catalog WHERE Type = 5)      
     FULL OUTER JOIN Catalog C2 ON DS.Link = C2.ItemID
WHERE C2.Type = 5 and C.Name IS NOT NULL
ORDER BY C2.Name ASC, C.Name ASC;

Here is the content of the Script Task:

Public Sub Main()
     Dim rs As New ReportingService2010()
     rs.Credentials = System.Net.CredentialCache.DefaultCredentials
     Try
          Dim reference As New DataSourceReference()
          reference.Reference = Dts.Variables("User::dataSourcePath").Value.ToString
          Dim source As New DataSource()
          source.Item = reference
          'The source name must be the data source name we used in the report.
          source.Name = Dts.Variables("User::dataSourceName").Value.ToString
          Dim sources As DataSource() = New DataSource(0) {}
          sources(0) = source
          rs.SetItemDataSources(Dts.Variables("User::reportPath").Value.ToString, sources)
     Catch e As SoapException
          Console.WriteLine(e.Detail.InnerXml.ToString())
          Dts.TaskResult = ScriptResults.Failure
     End Try
     Dts.TaskResult = ScriptResults.Success
End Sub

In summary, this technique can save you a lot of time, especially if you are migrating a large number of reports.  You can apply this to other migration scenarios as well, such as updating data source stored credentials and recently I used it to re-create subscriptions.