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.
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
