I want to download all my SSRS reports (and data sources) from my Reporting server, but not manually. Is there a solution?
 |
| My Reporting Server |
Solution
The easiest way to do this is with some PowerShell scripting and the SSRS webservice. The following PowerShell script recreates the SSRS folder structure on a local drive and downloads all reports (*.rdl) and all data sources (*.rds) to the corresponding folders.
Note 1: you need full access if you want to download everything
Note 2: empty folders are not recreated locally
Note 3: It only works for SSRS 2012 and higher (webservice differs for older versions)
1) PowerShell ISE
Open PowerShell ISE and copy and paste the code below. Determine the local folder (line 7) and provide the URL of the Reporting server on line 8 (without "/reports"). For this example the SSRS server is located on a Virtual Machine in Azure.
###################################################################################
# Download Reports and DataSources from a SSRS server and create the same folder
# structure in the local download folder.
###################################################################################
# Parameters
###################################################################################
$downloadFolder = "c:\temp\ssrs\"
$ssrsServer = "http://myssrs.westeurope.cloudapp.azure.com"
###################################################################################
# If you can't use integrated security
#$secpasswd = ConvertTo-SecureString "MyPassword!" -AsPlainText -Force
#$mycreds = New-Object System.Management.Automation.PSCredential ("MyUser", $secpasswd)
#$ssrsProxy = New-WebServiceProxy -Uri "$($ssrsServer)/ReportServer/ReportService2010.asmx?WSDL" -Credential $mycreds
# SSRS Webserver call
$ssrsProxy = New-WebServiceProxy -Uri "$($ssrsServer)/ReportServer/ReportService2010.asmx?WSDL" -UseDefaultCredential
# List everything on the Report Server, recursively, but filter to keep Reports and DataSources
$ssrsItems = $ssrsProxy.ListChildren("/", $true) | Where-Object {$_.TypeName -eq "DataSource" -or $_.TypeName -eq "Report"}
# Loop through reports and data sources
Foreach($ssrsItem in $ssrsItems)
{
# Determine extension for Reports and DataSources
if ($ssrsItem.TypeName -eq "Report")
{
$extension = ".rdl"
}
else
{
$extension = ".rds"
}
# Write path to screen for debug purposes
Write-Host "Downloading $($ssrsItem.Path)$($extension)";
# Create download folder if it doesn't exist (concatenate: "c:\temp\ssrs\" and "/SSRSFolder/")
$downloadFolderSub = $downloadFolder.Trim('\') + $ssrsItem.Path.Replace($ssrsItem.Name,"").Replace("/","\").Trim()
New-Item -ItemType Directory -Path $downloadFolderSub -Force > $null
# Get SSRS file bytes in a variable
$ssrsFile = New-Object System.Xml.XmlDocument
[byte[]] $ssrsDefinition = $null
$ssrsDefinition = $ssrsProxy.GetItemDefinition($ssrsItem.Path)
# Download the actual bytes
[System.IO.MemoryStream] $memoryStream = New-Object System.IO.MemoryStream(@(,$ssrsDefinition))
$ssrsFile.Load($memoryStream)
$fullDataSourceFileName = $downloadFolderSub + "\" + $ssrsItem.Name + $extension;
$ssrsFile.Save($fullDataSourceFileName);
}
There is also code (comment out) to provide a username and password in case you cannot use integrated security. Replace line 16 with the code on lines 11 to 13 and provide the username and password.
2) Result
Running the script in PowerShell ISE downloads all reports and data sources.
 |
| Downloading all reports |
Summary
This is a 'quick & dirty' script to download all SSRS reports at once. You can adjust the script to you own needs and make it more beautiful. A upload(/deploy) script will be provided in a next blog post.
