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.
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.
This comment has been removed by the author.
ReplyDeleteYour script makes reference to the variable $rptAbsoluteUrl numerous times, however you do not appear to have this variable defined. What should be the definition of this variable?
ReplyDeleteGood catch! apparently I removed this variable definition from the file when I was trimming a production version of it before posting to this blog, basically it's a location of each rdl file being deployed to a SharePoint folder and in my production version of the file it's defined this way:
ReplyDelete$rptAbsoluteUrl = [string]::Concat($targetRptLib.TrimEnd("/"),"/",$rptName)
As I see it this will not work if the logical name of a shared dataset is not the same as the file name. Also you are assuming that datasources are named as the database name. Finally, it can only be run if you know the exact location of an individual report, so you cannot just throw a folder with multiple reports and artifacts and have them hook up to one another as a result of this script. Am I correct?
ReplyDeleteYou're absolutely right, and I was not intending to cover all possible scenario with the metadata for the reports and their related objects. The current PS script worked well for me within the existent business requirements, and you can always add your extra logic if there is a need :-)
ReplyDeleteI feel satisfied to read your blog, you have been delivering a useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging. Power BI Online
ReplyDelete