SSRS Continuous Deployment PowerShell Script using ReportingServicesTools

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:

1. 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.
2. 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.
3. 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.
4. Deploy all reports to a single folder. If you want to deploy to multiple folders you'll have to modify the script.
5. 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. 

Install-Module -Name ReportingServicesTool  

Write this script to a text file with a .ps1 extension.

1:  # Script Parameters  
2:  Param(  
3:    [string]$ReportServerUri = "http://localhost/ReportServer",  
4:    [string]$RdlFolder = "C:\vsts-rel\SSISProjectWeaver-CI\44\reports",  
5:    [string]$RsPath = "/",  
6:    [string]$RsFolderName = "SSISProjectWeaver",  
7:    [string]$DatasourceName = "SSISExecutionManager",  
8:    [switch]$CleanReportServer  
9:  )  
10:    
11:  #Delete all objects  
12:  if ($CleanReportServer) {  
13:    Get-RsCatalogItems -ReportServerUri http://localhost/ReportServer -RsFolder '/' | Remove-RsCatalogItem -ReportServerUri http://localhost/ReportServer -Verbose -Confirm:$false  
14:  }  
15:    
16:  #Create SSRS folder   
17:  $targetFolder = Get-RsFolderContent -RsFolder $RsPath | Where-Object { $_.Name -eq $RsFolderName }  
18:  if ($targetFolder -eq $null)  
19:  {  
20:    New-RsFolder -ReportServerUri $ReportServerUri -Path $RsPath -Name $RsFolderName -Verbose  
21:  }  
22:    
23:  #Create full path  
24:  $Destination = $RsPath + $RsFolderName  
25:    
26:  #Change local dir to location of .rdls  
27:  CD $RdlFolder  
28:    
29:  #Deploy datasource  
30:  Dir $RdlFolder -Filter *.rds | Write-RsCatalogItem -ReportServerUri $ReportServerUri -Destination $Destination -OverWrite -Verbose  
31:    
32:  #Deploy all reports to single folder  
33:  Write-RsFolderContent -ReportServerUri $ReportServerUri -Path $RdlFolder -Destination $Destination -Verbose -Overwrite  
34:    
35:  $DataSourcePath = $Destination + "/" + $DatasourceName  
36:    
37:  # Set report datasource  
38:  Get-RsCatalogItems -ReportServerUri $reportServerUri -RsFolder $Destination | Where-Object TypeName -eq 'Report' | ForEach-Object {  
39:    $dataSource = Get-RsItemReference -ReportServerUri $ReportServerUri -Path $_.Path  
40:    if ($dataSource -ne $null) {  
41:      Set-RsDataSourceReference -ReportServerUri $ReportServerUri -Path $_.Path -DataSourceName $dataSource.Name -DataSourcePath $DataSourcePath  
42:    }  
43:  }  

Here is a sample call of the script.

C:\deploy_ssrs_reports.ps1 -ReportServerUri "http://localhost/ReportServer" -RdlFolder "\\my-build-share\reports" -RsPath "/" -RsFolderName "SSISProjectWeaver" -DatasourceName "SSISExecutionManager" -CleanReportServer