- Posted by Cláudio Silva
- On July 27, 2017
- 18 Comments
- Automation, Deployment, PowerShell, SQLServer, SSRS, syndicated
On my last post “SSRS Report Deployment Made Easy – 700 Times Faster” I showed how you can rely on the Microsoft PowerShell module ReportingServicesTools to automate several steps that otherwise would be a big time consumer.
After a couple of shares and re-tweets, my friend Rob Sewell (t) pointed that blog post to Annette Allen (t):
— Rob Sewell (@sqldbawithbeard) July 12, 2017
And her first question was:
Does that copy subscriptions too? https://t.co/1hzQVh8AAE
— Annette Allen (@Mrs_Fatherjack) July 12, 2017
What does this means?
If we take a look to the “New Subscription” form, we will discover about a dozen of fields that need to be configured. Doing this by hand can make you want to pull your hairs, also the probability of error is huge, even with copy & paste.
Who wants to do copy & paste of dozens of fields between reports? I know who doesn’t – me 🙂
My contribution: 3 new commands that help handle subscriptions
Retrieves information about subscriptions for a report.
Set a new reporting subscription based on the info of an existing subscription (retrieved using Get-RsSubscription ).
You can choose a specific report or pass a folder. When using a folder, the report must have the same name.
NOTE: A new subscriptionId will be generated.
Removes a subscription from the report.
See them in action
Retrieves all existing subscriptions on Report1 inside folder ReportingServicesTools
Get-RsSubscription -ReportServerUri $reportServerUri -Path "/ReportingServicesTools/Report1"
If we already have the same report on another location we can copy all (or just some) subscriptions to them. Let’s say we have a folder RSTools with Report2 inside. To copy all subscriptions (two in this case) with all configurations we can run a line of code like this:
Get-RsSubscription -ReportServerUri $reportServerUri -Path "/ReportingServicesTools/Report1" | Set-RsSubscription -ReportServerUri $reportServerUri -Path "/RSTools/Report2"
Also, we can pass the
-RsFolder and if exists a report with the exactly same name, that will be the destination of the subscriptions.
The following example show how you can remove all subscriptions from one report.
Get-RsSubscription -ReportServerUri $reportServerUri -Path "/RSTools/Report2" | Remove-RsSubscription -ReportServerUri $reportServerUri -Verbose
Remove-RsSubscription -ReportServerUri $reportServerUri -SubscriptionId "73eb1d24-1e14-4a80-a034-a7738089beba" -Verbose
Working with SSRS subscriptions became easier.
Hopefully this will help you to get, copy and remove subscriptions in a much more efficient way!
Try it and let me know what do you think.
NOTE: I’m sure this will not address all the needs out there! This is a starting point.
You can open an issue on ReportingServicesTools GitHub repository to report any bug you have found or even to request a new feature.
PS: A special thanks to Jaime Tarquino for the help with reviewing the code and publish the new version of ReportingServicesTools.
PS2: For those asking how much time we can save, creating a new subscription replicating all values (including values for 2 parameters) took me about 45 seconds.
Using this commands took less than 1.1 seconds to copy the exact same subscription:
This translates into more than 4000 times faster creation.