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):

https://t.co/VdOMP4HqIL might be useful @Mrs_Fatherjack cc @ClaudioESSilva

— 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

Glup! I think not. To be honest, I didn’t need to handle subscriptions in my scenario! So, let’s look if we have any functions to handle it in the current module:

Hmm…Nada! Zero! 0!

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

Get-RsSubscription

Retrieves information about subscriptions for a report.

Set-RsSubscription

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.

Remove-RsSubscription

Removes a subscription from the report.

This three commands are available since version v0.0.2.2 (published today – 27 Jun 2017)

Now we can find subscription commands:

See them in action

Getting subscriptions:

Retrieves all existing subscriptions on Report1 inside folder ReportingServicesTools

Get-RsSubscription -ReportServerUri $reportServerUri -Path "/ReportingServicesTools/Report1"

Copying subscriptions:

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.

Removing 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

Also, you can just delete one specific subscription. For that you should use the

-SubscritpionId

parameter:

Remove-RsSubscription -ReportServerUri $reportServerUri -SubscriptionId "73eb1d24-1e14-4a80-a034-a7738089beba" -Verbose

Wrap up

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.