Search This Blog

Thursday, May 14, 2015

Powershell for SSRS in SharePoint: Multiple data sources and shared datasets

I thought that I would leave any further attempts to improve my Powershell script that was used to deploy SSRS reports to SharePoint along with creating data driven subscriptions in there (please see my previous blog posts: PowerShell, help me to publish my SSRS reports to SharePoint!, SSRS reports in SharePoint mode, Saga #2: PowerShell generates email subscriptions). 
  
But recent requirement change required to enable support a mapping multiple data sources for a single report along with proper shared datasets mapping (you know, that you can reference a shared dataset and then put a different alias name within a report). 

So here is a part of my new script that maps all the available data sources within an SSRS report: 


and here a part of the script that "wisely" maps all report's shared datasets to real referenced datasets after they are deployed to a destination environment: 




Basically I had to look into report RDL XML file, get a list of shared datasets and then map each dataset reference to their corresponding names. In the past we had a constraint to keep report datasets' alias names equal with the real names of shared datasets, but sometimes there may be a need to reuse a very same shared dataset and name it differently.

And here is a link for the complete SSRS PowerShell script that could be reused with some slight modifications depending on your needs. This last modification was inspired by (http://hindenes.com/powershell/SQLReporting.psm1 & http://ssrsreconciliate.codeplex.com/), and I just needed to share my "love" of reading through report RDL file XML tags :-)

The other thing I realized using my PowerGUI tool for scripting was that it couldn't upload SSRS RDL files to SharePoint which were greater then 1 Mb (Error: Out of memory), so I started using Windows PowerShell ISE (which has some limitations in script debugging, however Out of Memory error message no longer occurred).

Happy data adventures!