Skip to main content


SSRS SharePoint Mode Migration Scripting

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:

     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

Here is the content of the Script Task:

Public Sub Main()
     Dim rs As New ReportingService2010()
     rs.Credentials = System.Net.CredentialCache.DefaultCredentials
          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
          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.

Thoughts on “SSRS SharePoint Mode Migration Scripting”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Kevin Poust

More from this Author

Follow Us