SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

“Does that copy subscriptions too?” – Now it does! New PowerShell SSRS commands

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


And her first question was:


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.

Cláudio Silva

I am a SQL Server DBA working from SQL2000 to SQL2016. I love using PowerShell to make my life ridiculously easier! I contribute to dbatools PowerShell open source module.

Comments

Leave a comment on the original post [redglue.org, opens in a new window]

Loading comments...