Download all SSRS reports

Question

I want to get a copy of all .rdl files in one server. I can do the download manually one report at the time, but this is time consuming especially that this server has around 1500 reports.

Is there any way or any tool that allows me to download all the .rdl files and take a copy of them?

Solution 1

There is a complete & simpler way to do this using PowerShell.

This code will export ALL report content in the exact same structure as the Report server. Take a look at the Github wiki for other options & commands

#------------------------------------------------------
#Prerequisites
#Install-Module -Name ReportingServicesTools
#------------------------------------------------------

#Lets get security on all folders in a single instance
#------------------------------------------------------
#Declare SSRS URI
$sourceRsUri = 'http://ReportServerURL/ReportServer/ReportService2010.asmx?wsdl'

#Declare Proxy so we dont need to connect with every command
$proxy = New-RsWebServiceProxy -ReportServerUri $sourceRsUri

#Output ALL Catalog items to file system
Out-RsFolderContent -Proxy $proxy -RsFolder / -Destination 'C:\SSRS_Out' -Recurse

Solution 2

I've created this powershell script to copy them into a ZIP. You have to provide the SQL server database details.

Add-Type -AssemblyName "System.IO.Compression.Filesystem"

$dataSource = "SQLSERVER"
$user = "sa"
$pass = "sqlpassword"
$database = "ReportServer"
$connectionString = "Server=$dataSource;uid=$user; pwd=$pass;Database=$database;Integrated Security=False;"

$tempfolder = "$env:TEMP\Reports"
$zipfile = $PSScriptRoot + '\reports.zip'

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()

$allreports = $connection.CreateCommand()
$allreports.CommandText = "SELECT ItemID, Path, CASE WHEN Type = 2 THEN '.rdl' ELSE '.rds' END AS Ext FROM Catalog WHERE Type IN(2,5)"

$result = $allreports.ExecuteReader()

$reportable = new-object "System.Data.DataTable"
$reportable.Load($result)
[int]$objects = $reportable.Rows.Count
foreach ($report in $reportable) {
    $cmd = $connection.CreateCommand()
    $cmd.CommandText = "SELECT CAST(CAST(Content AS VARBINARY(MAX)) AS XML) FROM Catalog WHERE ItemID = '" + $report[0] + "'"
    $xmldata = [string]$cmd.ExecuteScalar()
    $filename = $tempfolder + $report["Path"].Replace('/', '\') + $report["Ext"]
    New-Item $filename -Force | Out-Null
    Set-Content -Path ($filename) -Value $xmldata -Force
    Write-Host "$($objects.ToString()).$($report["Path"])"
    $objects -= 1
}

Write-Host "Compressing to zip file..."
if (Test-Path $zipfile) {
    Remove-Item $zipfile
}
[IO.Compression.Zipfile]::CreateFromDirectory($tempfolder, $zipfile) 

Write-Host "Removing temporarly data"
Remove-Item -LiteralPath $tempfolder -Force -Recurse

Invoke-Item $zipfile

Solution 3

If you just need this for backup purposes or something similar, this might be useful: Where does a published RDL file sit?

The relevant query from that thread is:

select convert(varchar(max), convert(varbinary(max), content))
from catalog
where content is not null

The original answer was using 2005, and I've used it on 2016, so I imagine it should work for 2008 and 2012.

When I had to use this, I added in the Path to the query as well, so that I knew which report was which.

CAVEAT: prior to SSMS v18, Results to Grid is limited to 64KB per tuple and Results to Text are limited to 8,192 characters per tuple. If your report definition is larger than these limits you will not be able to get the entire definition.

In SSMS v18, those limits have been increased to 2MB per tuple for both Reports to Grid as well as Results to Text.

Solution 4

This is based on SQL2016/SSRS2016 but I think it should work for 2012.

SELECT 'http://mySQLServerName/reports/api/v1.0/catalogitems(' + cast(itemid as varchar(256))+ ')/Content/$value' AS url
    FROM ReportServer.dbo.Catalog

This will give you a list of URL's, one for each report.

If the above did not work in SSRS 2012 then go to the report manager and do as if you were going to download the file from there. Check the URL on the download button and you'll probably see a URL with and item id embedded int it. Just adjust the above code to match that url structure.

What you do with then after this is up to you. Personally I would use the Chrome extension called 'Tab Save' available in the Chrome store here. You can simply copy and paste all the URL's created above into it and hit the download button...

Solution 5

Found and used this without any issues. Nothing to install, just added my url, and pasted into Powershell.

https://microsoft-bitools.blogspot.com/2018/09/ssrs-snack-download-all-ssrs-reports.html

In case the link breaks, here's the code from the link:

###################################################################################
# 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);
}

View more solutions

Comments

[DrewB]

I wish I could upvote this 10 times. You just saved me several hours of work.

[Geoff Griswald]

putting the $sourceRsUri in single quotes didn't work for me, I had to use double quotes

[Viktor]

I was getting Unable to find type [Microsoft.ReportingServicesTools.ConnectionHost] - this is defined in the ReportingServicesTools package. Turned out the root cause was running the script from a network path. Once I ran it from my C drive, it worked fine.

[Jeremy F.]

Works for both SSRS 2017 and 2019 too! For SSRS 2017 - $sourceRsUri = 'ReportServerURL/ReportServer/ReportService2017.asmx?wsdl' For SSRS 2019 - $sourceRsUri = 'ReportServerURL/ReportServer/ReportService2019.asmx?wsdl'

[Nii]

i'm having a weird one. i specified $sourceRsUri to look at /ReportService2012.asmx?wsdl because i'm running SSRS 2012 but when running the script, it keeps looking at /ReportService2010.asmx?wsdl and therefore throwing an error.

[Nii]

this ended up helping me instead of the accepted answer. so thanks!

[CPorteous]

Hey Nii, ssrs 2012 s ill uses the 2010 asmx so you need to keep it set to that. Do you see another error when using that?

[Stuart J Cuthbertson]

FYI: This PowerShell module does not work with PowerShell Core: github.com/microsoft/ReportingServicesTools/issues/…

[Stuart J Cuthbertson]

Another FYI: if anyone else is still crazy enough to have SSRS 2005 running - this won't work out-of-the-box as the SOAP API was quite different back then. I was able to hack the behaviour of Out-RsFolderContent and Out-RsCatalogItem to just download reports successfully (not Data Sources etc).