Tuesday, May 1, 2018

Way back in 2010 I wrote a post about how to use RS.exe to automate the publishing of SSRS objects such as reports, datasource, folders, etc. I got familiar with RS.exe at that time because I needed a way to include SSRS object deployment in a commercial software installer package. This time around I had a similar requirement except that the deployment is now for a CD (continuous deployment) pipeline for an open source project that I am working on called SSIS Project Weaver. It is an SSIS execution framework that includes a number of SSRS reports used to monitor and administer ETL batches.

Instead of using my old tricks I decided to see if there was anything else new out there. Turns out there is. Microsoft was kind enough to release a PowerShell-based project called ReportingServicesTools which targets SQL Server 2012+. Once installed, this PowerShell module provides 40+ commands for interacting with an SSRS instance. Not unlike RS.exe, its commands are not always straightforward.

I won't go into detail about what each command does as there's plenty of existing documentation on that. What I will do is post the PS script I'm using specifically for my CD scenario.

My requirements were the following:

Wipe all objects from SSRS instance. Keep in mind that the objective of this script is to test the build and deployment of the source code of my project, not deploy to a production environment. Just in case I have other projects being deployed to the same server I created a switch so that I can choose whether or not to execute this portion of the script.

Create a folder in which to deploy the reports. This is not a straightforward as one would hope. The ReportingServicesTools command does not provide an option to "force" or overwrite if the folder already exists. If the folder already exists the command fails.

Deploy the datasource(s). Using PowerShell piping and object filtering we can deploy all of our datasources with a single reference to, but multiple executions of, the Write-RsCatalogItem command.

Deploy all reports to a single folder. If you want to deploy to multiple folders you'll have to modify the script.

Set all report's datasources.

Before you can use the ReportingServicesTools commands and use the script below you must install the PS module by running the following command.