2

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.

  • There are different ways of migrating your SSRS reports to another one. I wrote this blog that uses the reporting server databases backup and restore to achieve this. Link: samosql.com/2017/10/25/migration-of-ssrs-to-another-instance Hope that helps and gives you a lead. Let me know of additional questions or concerns. – samosql Apr 18 '18 at 15:24
1

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:

enter image description here

The original article on that tool can still be found on archive.org Download Reporting Services Scripter

0

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)

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy