Deploying Report Server Content via PowerShell

One of the most common set of operations any user performs on a new installation of SQL Server Reporting Services 2017 or Power BI Report Server (October 2017) is uploading content. Today most people do this using the Report Portal. However, this approach doesn’t yield great results when you have to deploy tens, hundreds or even thousands of items as you can only upload 1 item at a time in Report Portal.

In this blog post, I am going to cover the new PowerShell commands you can use to deploy content to Report Servers.

Prerequisites

The primary assumption made in this blog post is your Report Server is either SQL Server Reporting Services 2017 or Power BI Report Server (October 2017). The reason behind this is the commands described here use the new REST APIs that were first shipped in these releases. These APIs have been documented at https://app.swaggerhub.com/apis/microsoft-rs/SSRS/2.0 for SSRS 2017 and https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0 for PBIRS October 2017.

You will also need ReportingServicesTools, which you can either clone from https://github.com/Microsoft/ReportingServicesTools or download the latest stable version of this module from the PowerShell Gallery (https://www.powershellgallery.com/packages/ReportingServicesTools). Since its inception, the project has gone from a set of individual PowerShell scripts to a full blown PowerShell Module with new commands being added for all sorts of operations including:

  • admin operations (like backing up encryption key, configuring email settings, etc.)
  • management operations (like publishing items, setting data source credentials,  managing subscriptions, etc.)
  • security operations (granting/revoking access to server and on individual items)

Deploying Content to Report Server

Deploying content individually

First I recommend you to create a new WebSession using the following command. While creating a WebSession is not necessary, by doing so, you won’t have to enter the Report Portal URI in all the other commands. To create a WebSession, you run the following:

$session = New-RsRestSession -ReportPortalUri http://localhost/reports_pbi

Next you need to decide where you want to deploy your content. Do you want it to be in the root folder? Or some nested folder? If you want it to be in a nested folder, you can create a new folder using the following command:

New-RsRestFolder -WebSession $session -RsFolder "/My Folder"

Finally you need to run the following command to publish your content to Report Server:

Write-RsRestCatalogItem -WebSession $session -Path "C:\Users\admin\Documents\Reports\SimplePowerBIReport.pbix" -RsFolder "/My Folder"

Deploying all resources in a folder

Okay, so the steps in the previous section seemed rather verbose. While it is better than you manually uploading 1 item at a time in the Report Portal, the process can still be improved.

Just like before, you should first create your WebSession.

$session = New-RsRestSession -ReportPortalUri http://localhost/reports_pbi

Now, the following command will upload all the resources found in a folder:

Write-RsRestFolderContent -WebSession $session -Path "C:\Users\admin\Documents\Reports" -RsFolder "/My Folder"

Great! But before I let you go, I have one more trick to show you. If you specify -Recurse flag in the previous command, then the command will create a new folder on Report Server for every folder discovered under the original folder and will also upload all of the content under that folder.

Write-RsRestFolderContent -WebSession $session -Path "C:\Users\admin\Documents\Reports" -RsFolder "/My Folder" -Recurse

So you could literally the folder layout under which you want the content to be stored as on Report Server, and this command will push it to the Report Server in the exact same way.

7 Comments

Add a Comment

Your email address will not be published. Required fields are marked *