Life is full of opportunities, it only takes to notice and use them. About 3 months ago I wrote a post about deploying reports using a PowerShell script (PowerShell, help me to publish my SSRS reports to SharePoint!) and at my new project I proposed to apply the very same technique of deploying reports to different environments. There was only one new thing to this, each of the SSRS reports at this new project had several data driven subscriptions (emails, file shares) and to recreate them manually, it would take a big junk of your life time.
So, that was my new opportunity that I didn't want to miss and decided to enhance my original PowerShell script even more, why not, let's utilize PowerShell potential and bring SSRS and SharePoint even closer.
Having this said, here is the complete PowerShell script (PS_Subscriptions) that does:
- Deploy SSRS datasources, datasets, reports,
- Maps all datasources and datasets for reports when it's necessary,
- and Creates report data subscription.
Main knowledge on how to automate SSRS subscription came from CreateDataDrivenSubscription method of the ReportingService assembly; also be sure to check this Microsoft knowledge base article (http://support.microsoft.com/kb/842854) that shows some of the corrections that were applied to the original examples of the CreateDataDrivenSubscription method.
Let's start from the main changes to the original script. I created two new functions that removes old subscriptions and creates new ones (CreateDataDrivenSubscription method doesn't overwrite old subscription with the same name, it just creates same name subscription with different subscription IDs):
Also, don't forget to specify a necessary delivery extension for SSRS reports, because there are 3 of them available (Report Server Email, Report Server DocumentLibrary, Report Server FileShare):
Main steps to create a new SSRS report subscription can be described with these steps:
1) Set delivery extension setting
2) Create the data source for the delivery query with data query and fields
3) Define subscription schedule
4) Set parameter values
5) Execute CreateDataDrivenSubscription method
It's been a great experience to apply PowerShell techniques in creating SSRS report subscriptions; and I've spent a few days writing this code; however I'm sure that more experienced PS developers could accomplish this task in a few hours. Nevertheless, I've learned a few lessons and would like to share them with you.
Lessons learned:
a) If you create a new datasource, then use DataSourceDefinition; otherwise if it's a shared data source use DataSourceReference.
b) Don't use parameters labels (as they're shown in SharePoint), but use real parameters names:
d) Delivery Extension parameter values are case sensitive; for Email and Sharepoint file location they're different: for example, "IncludeLink" for Email and "RENDER_FORMAT" for SharePoint file.
e) Weird error about method not being supported; check all the variables, parameters format and value and test your script again.
I've been using PowerGUI tool (http://en.community.dell.com/techcenter/powergui/m/bits/) to develop and test all of my PowerShell scripts, perhaps you can suggest other PS GUI environments. But in the meantime, I continue wishing you happy data adventures!
So, that was my new opportunity that I didn't want to miss and decided to enhance my original PowerShell script even more, why not, let's utilize PowerShell potential and bring SSRS and SharePoint even closer.
Having this said, here is the complete PowerShell script (PS_Subscriptions) that does:
- Deploy SSRS datasources, datasets, reports,
- Maps all datasources and datasets for reports when it's necessary,
- and Creates report data subscription.
Main knowledge on how to automate SSRS subscription came from CreateDataDrivenSubscription method of the ReportingService assembly; also be sure to check this Microsoft knowledge base article (http://support.microsoft.com/kb/842854) that shows some of the corrections that were applied to the original examples of the CreateDataDrivenSubscription method.
Let's start from the main changes to the original script. I created two new functions that removes old subscriptions and creates new ones (CreateDataDrivenSubscription method doesn't overwrite old subscription with the same name, it just creates same name subscription with different subscription IDs):
Also, don't forget to specify a necessary delivery extension for SSRS reports, because there are 3 of them available (Report Server Email, Report Server DocumentLibrary, Report Server FileShare):
Main steps to create a new SSRS report subscription can be described with these steps:
1) Set delivery extension setting
2) Create the data source for the delivery query with data query and fields
3) Define subscription schedule
4) Set parameter values
5) Execute CreateDataDrivenSubscription method
It's been a great experience to apply PowerShell techniques in creating SSRS report subscriptions; and I've spent a few days writing this code; however I'm sure that more experienced PS developers could accomplish this task in a few hours. Nevertheless, I've learned a few lessons and would like to share them with you.
Lessons learned:
a) If you create a new datasource, then use DataSourceDefinition; otherwise if it's a shared data source use DataSourceReference.
b) Don't use parameters labels (as they're shown in SharePoint), but use real parameters names:
c) Lack of matchdata schema explanation; look at your ReporServer backend table for the XML tag examples in order to understand a schedule creation.
d) Delivery Extension parameter values are case sensitive; for Email and Sharepoint file location they're different: for example, "IncludeLink" for Email and "RENDER_FORMAT" for SharePoint file.
e) Weird error about method not being supported; check all the variables, parameters format and value and test your script again.
I've been using PowerGUI tool (http://en.community.dell.com/techcenter/powergui/m/bits/) to develop and test all of my PowerShell scripts, perhaps you can suggest other PS GUI environments. But in the meantime, I continue wishing you happy data adventures!
Comments
Post a Comment