Moving SSRS Reports between servers

Today, my friend Derik Hammer (blog|twitter) asked about moving SSRS reports between (versions and) instances of SSRS:

What is the best way to move 2008 R2 SSRS reports into an existing 2012 SSRS instance? Catalog move is insufficient here. #sqlhelp

This reminded me of a PowerShell script I wrote a few years back, so I’m dusting it off for this blog post. I’m far from a PowerShell Pro, but I think this will be helpful to other people

PowerShell + SSRS

We’re just interacting with the SSRS Web Service. I’m using PowerShell, but you could also write a little .NET app to do something similar.

I’m not using any magical SSRS-specific cmdlets. I’m using PowerShell to interact with a web service….that web service just happens to be SSRS.

Two SSRS web service methods

There are two methods from the SSRS web service that I use in my script:

GetItemDefinition - Like the name implies, this method is used to get the item's definition from SSRS. If it's a report item, then we're talking about the .rdl file. If it's a data set, then it's the .rsd file.
CreateCatalogItem - This one is pretty self-explanatory, too. If you have an item definition, this is the method you'll use to create it on a server.

### One PowerShell cmdlet There's only one PowerShell cmdlet that I use. I use New-WebServiceProxy to get PowerShell to talk to those web service methods. That's it... ### The process The process is pretty straightforward:

Build URLs to the SSRS web service for both the source & destination servers.
Use those URLs to create WebServiceProxy objects (using New-WebServiceProxy)
Pull the report definition (or other item definition) by using the source WebServiceProxy object to call GetItemDefinition.
Push the report definition (or other item definition) by using the destination WebServiceProxy object to call CreateCatalogItem.

That's it. I also parameterized this script so that I could call it from another process, passing in the salient bits. ### The Code There are comments in-line to walk you through the code, so I'll let the comments do the talking. Some of the steps could certainly be combined, but I specifically broke them out into separate steps for clarity.

Use the comments section to let me know if this is helpful, or if you have any other feedback.

#
# DeploySSRSReports.ps1
# 
param([String] $SourceServer,
      [String] $SourceReportPath,
      [String] $ItemName,
      [String] $ItemType = "Report",
      [String] $DestinationServer,
      [String] $DestinationReportPath,
      [String] $CommitSvn 
    )

####################################################################################################
# AUTHOR: Andy Mallon
# CREATED: 20130322
#   This PoSh script deploys an SSRS Report from one server to another.
#     This can be wrapped in a loop to migrate an entire server
# 
# PARAMETERS:
#   $SourceServer            DNS name of the SSRS server where the report to be deployed lives
#   $SourceReportPath        The path on the source server for the report that should be deployed
#   $ItemName                Name of report to be deployed
#   $ItemType                Report (rdl), DataSet (rsd), DataSource; defaults to Report
#   $DestinationServer       DNS name of the SSRS server where the report will be deployed to
#   $DestinationReportPath   The path on the destination server where the report should be deployed to
#
# EXAMPLES:
#  * Deploy & commit to SVN the report "Performance Report" from the directory "/DEV/Andy Mallon/"
#          on server DBDEVREPORT ... and deploy to the directory 
#          "/DBA/Performance" on server DBREPORT
#  D:\SSRS> DeploySSRSReports.ps1 -SourceServer {DBDEVREPORT} -SourceReportPath {/DEV/Andy Mallon} 
#                            -ItemName {Performance Report} -DestinationServer {DBREPORT} 
#                            -DestinationReportPath {/DBA/Performance} -CommitSvn {1}
#
####################################################################################################
# MODIFICATIONS:
#   20130510 - AM2 - Rename $ReportName to $ItemName
#                  - Add functionality to deploy DataSets, DataSources & Reports
####################################################################################################


# Make $SourceReportPath the full path including file name
$SourceReportPath = $SourceReportPath + "/" + $ItemName

# Build URIs for ReportServer Services.
$sourceServerUrl = "http://" + $SourceServer + "/ReportServer/ReportService2010.asmx" 
$DestinationServerUrl = "http://" + $DestinationServer + "/ReportServer/ReportService2010.asmx" 

# Create web service objects from URLs
$SourceProxy = New-WebServiceProxy -Uri $sourceServerUrl -Namespace SSRS.ReportingService2010 -UseDefaultCredential
$DestinationProxy = New-WebServiceProxy -Uri $DestinationServerUrl -Namespace SSRS.ReportingService2010 -UseDefaultCredential

# Pull report definition from source
$ReportDefinition = $SourceProxy.GetItemDefinition($SourceReportPath)

# Push report definition to target
# use try-catch so that we can return/exit on failure
$warnings = $null
try{
$DestinationProxy.CreateCatalogItem($ItemType,$ItemName,$DestinationReportPath,$true,$ReportDefinition,$null,[ref]$warnings)
}
catch{
Return "***** MIGRATION FAILED *****"
}

#########################################
# Report is deployed now. 
#########################################