Moving SSRS Reports between servers

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

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. 
#########################################