Blog Post

PowerShell, help me to publish my SSRS reports to SharePoint!

,

Every time when I heard the word 'PowerShell' it was just like a call to Terra Incognito, you know nothing about it and the longer you stay away the more interesting it becomes to learn. You think of people who could create PS scripts to install and setup complicated systems in a single run and maybe of some others who dreamed of starting engine of their cars with the help of PowerShell (as I heard about this at one of our local Toronto SQL Server user group meetings).

There are many different PowerShell training materials available in the internet, and it only takes time and a little efforts to start learning and practicing this technology at your every walk of you life (I've read it and it made me laugh of how PowerShell has become a panacea for everything :-). Well anyway, I've kept telling myself for a very long time that someday, somehow I would get into this and would get at least a sound knowledge to start applying it at some of my database related activities.

So this day has come and at my company we were deploying a big number of SSRS reports to a SharePoint application site. I know that you can do reports deployment to SharePoint from Visual Studio and manually publish RDL files too; the latter skill I've mastered with no basic training at all 🙂 Our SharePoint developers handed me a PowerShell script that did exactly was I was looking for: publishing all the datasources, datasets and reports; however all mappings between them all were left unset and you had manually specify all datasets and datasources for already published reports; and it does takes a lot of time!!!

With the help of a few articles in the internet:
PowerShell:Deploying SSRS Reports in Integrated Mode
SSRS SharePoint Foundation 2010 Integration - Deployment Query

and MSDN resource (http://technet.microsoft.com/en-us/library/reportservice2010.aspx) that lets you to explore all the details of the ReportService2010 Web service, I was able to understand how PowerShell could help me.

The main issue I've had was that the ReportService2006 web service didn't have a method to map datasets for reports, but the next version of this web service now had this option.

After my long trials and tribulations to build a single script that would do all the steps of SSRS reports deployment in a single run were over, then the main section of my script looked this way:

and complete script can be downloaded from here: DeploySSRS_DataSource_DataSet_Report

You can adjust this script any way you need to: set variables values your own way, change routine logic, adjust output messages, etc. And again, I would like to repeat myself, then I didn't create most parts if this script, however after reading numerous articles and forum threads related to PowerShell; I made it running in the 2010 version of the ReportServer web service and successfully tested it in several SharePoint environments.

I hope you'll find this script useful and provide more improvements and modifications.

Enjoy your day and happy data adventures!

PS. Link to my post with the next developments of this script - SSRS reports in SharePoint mode, Saga #2: PowerShell generates email subscriptions.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating