I'm looking to export SSRS reports from SSRS 2005 into SSRS 2017. I have around 100 reports to move but in total there are around 500 subscriptions across all of these reports.
The Script to file tool in SSMS 2005 would save a large amount of time however is there a way to programmatically call the Script to file function?
I know you can do something similar with Powershell to export databases.
I'm not sure how well powershell would handle the guids for the reports when you only transfer the jobs, but you could use rs.exe to execute a script file and call the webservice to download/copy/deploy Reporting Services Objects.
On the Sample Reporting Services rs.exe Script to Copy Content between Report Servers it is stated that Shared Schedules are supported for both native and sharepoint integrated instances.
There used to be a tool called "rsscripter" that provided a gui to create the scripts but I'm not sure how well it will work with more recent versions. It's included in the SQL Server fine build and looks like this:
The original article on that tool can still be found on archive.org Download Reporting Services Scripter
I wrote this function to facilitate me doing SQL Server Migrations, but it does take the agent jobs on one server and writes them to disk as .sql files.
Function Write-SQLAgentJobs([string]$ServerInstance, [string]$ExportPath)
{
#Create Directory if needed.
If((Test-Path -LiteralPath $ExportPath -PathType Container) -eq $false)
{
New-Item -Path $ExportPath -ItemType Container | Out-Null
Write-Debug -Message "$(Get-Date) - Creating Export Path $($ExportPath)"
}
#Get the Additional Modules needed for doing the work.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$SMO = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerInstance
#Build Up Scripter Object...
$Scripter = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMO)
$ScriptOptions = New-Object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')
$Scripter.Options.ScriptDrops = $False
$Scripter.Options.WithDependencies = $False
$Scripter.Options.IncludeHeaders = $True
$Scripter.Options.AppendToFile = $False
$Scripter.Options.ToFileOnly = $True
$Scripter.Options.ClusteredIndexes = $True
$Scripter.Options.DriAll = $True
$Scripter.Options.Indexes = $True
$Scripter.Options.Triggers = $True
$Scripter.Options = $ScriptOptions
ForEach($Job In ($SMO.JobServer.Jobs | Where-Object -FilterScript {$_.name -ne ""}))
{
$JobName = ConvertTo-ValidPath -TestPath ($Job.name)
$FileName = [System.IO.Path]::Combine($ExportPath, "$($JobName).sql")
If((Test-Path -LiteralPath $FileName -PathType leaf) -eq $true)
{
Remove-Item -LiteralPath $FileName -Force -ErrorAction SilentlyContinue | Out-Null
}
$Scripter.Options.FileName = $FileName
$Scripter.Script($Job) | Out-Null
}
}
Function ConvertTo-ValidPath([string]$TestPath)
{
$invalidChars = [IO.Path]::GetInvalidFileNameChars() -join ''
$re = "[{0}]" -f [RegEx]::Escape($invalidChars)
Write-Output ($TestPath -replace $re)
}
