SSRS has nice little feature where you can automate emailing of an report on a pre-defined schedule. Last week, one of our customer was moving their SQL server to brand new one and wanted to move all SQL related feature to it.
So I have moved everything and was left with few dozens of report subscriptions. I wondering whether I should re-create them manually on new server or is there a tool that I can use.
When asked from AI search tool, it came up with several tools. Out of them following two were my finalists.
1. Report Sync Tool -> https://code.google.com/archive/p/reportsync/downloads
2. ReportingServicesTools written in PowerShell
I decided to go with PowerShell, because of my love with PowerShell.
ReportingServicesTools is a PowerShell cmdlet is a tool that we can use to do various tasks related to SSRS. It is hosted on GitHub -> ReportingServices
Documentation contains how to install it and basic use of it. However, I couldn't find enough documentation regarding how to do a report subscription migration.
There were some information in here, but nothing much.
So I decided to dig little deep into the script and see what is it capable of. I was working with version 0.0.8.0.
There are about 70 odd functions in that cmdlet, but I was interested on functions relate to subscriptions.
- Get-RsSubscription
- Set-RsSubscription
- Copy-RsSubscription
- Export-RsSubscriptionXml
- Remove-RsSubscription
- New-RsSubscription
- Import-RsSubscriptionXml
In order to fetch subscriptions under a reporting folder I used following:
Get-RsSubscription -ReportServerUri 'http://remote-machine:8080/reportserver_sql16' -RsItem '/path/to/my/report'
In -RsItem parameter you can specify a folder or a report it self. If you specify a folder, it will look for all subscriptions under that folder. If you specify a report, it will look for all subscription belong to that report only.
Above assume current windows user credentials when connecting to report server. But if you want to connect to report server using different credentials use the usual methods to specify credentials for -Credential parameter.
$password = ConvertTo-SecureString "MyPlainTextPassword" -AsPlainText -Force
$Cred = New-Object System.Management.Automation.PSCredential ("username", $password)
Now I got subscriptions, I want to write them to a file, so I can transfer them to the new server (if the new server is accessible from where you are you can directly use the Copy-RsSubscription function).
To export to a file, I have used Export-RsSubscriptionXml function.
Get-RsSubscription <<parameters as per above>> | Export-RsSubscriptionXml C:\Test\MySubscriptions.xml
You can pipe the output of the Get-RsSubscription function to Export function.
Then on the new server, I have copied above xml file to a directory and used Import-RsSubscription to import them to new Reporting server.
Import-RsSubscriptionXml C:\Test\\MySubscriptions.xml -ReportServerUri 'http://remote-machine:8080/reportserver_sql16'
This will just create Powershell objects from the xml file, you need to output this to a Copy-RsSubscription or Set-RsSubscription function.
Different between Copy and Set functions are, Copy create new subscriptions, where set updates already existing subscription.
If you want just to see, what is in a xml file you can use command like below:
Import-RsSubscriptionXml .\MySubscriptions.xml |
Out-GridView -PassThru |
Copy-RsSubscription -RsItem /Example/Report
You can learn more about these functions, from source code help available on the GitHub -> CatalogItems.
