Migrating SSRS 2016 Content via PowerShell

Generally, users have 2 different Report Server environments: test and production. The test environment is for designing, iterating and stabilizing new content while the production environment is for consuming that content. In the past, users used SQL Server Data Tools in Visual Studio to achieve this. However, with the introduction of Mobile Reports in SQL Server Reporting Services 2016 and the addition of Power BI Reports and Excel Workbooks in Power BI Report Server, a new solution is needed in migrating content between environments.

In this blog post, I am going to focus on this scenario. Due to differences in APIs between SQL Server Reporting Services 2016 and SQL Server Reporting Services 2017/Power BI Report Server, I will focus on Migration in SQL Server Reporting Services 2016 in this blog post and will release another blog post on Migration in SQL Server Reporting Services 2017 and Power BI Report Server (October 2017).

Prerequisites

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

Migrating Content for SQL Server Reporting Services 2016

The general process behind migration will involve downloading the content from test server to some temporary location on local machine and then deploying all items in the temporary location to the destination server. Ideally, it would be awesome if content could be migrated without having to be downloaded somewhere, but currently migration will require downloading the content.

There are 2 types of API you can use to download and upload content in SQL Server Reporting Services 2016: 1) REST API and 2) SOAP API. For traditional Report Server content like Shared Data Sources, Shared Data Sets, Paginated Reports and Resources, you can download and upload content using the SOAP API. However, if you want to migrate Mobile Reports, the only way you can migrate them is via the v1.0 of the REST API. You can also use REST API to download and upload ALL Report Server Content: Shared Data Sources, Shared Data Sets, Paginated Reports, Resources and Mobile Reports.

1) REST API

But before I go into the details of how to use the REST API, I want to make a very important disclaimer. v1.0 of REST API is NOT supported by Microsoft. Having said that, the commands below work without any issues. However, if you come across any issue with them, I recommend you to open an issue on ReportingServicesTools GitHub project.

Downloading Content via REST API

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:

$testSession = New-RsRestSession -ReportPortalUri http://ssrs-test/reports -RestApiVersion v1.0

Next you need to decide where you want to download the content to. In the following example, I am downloading a Mobile Report (called MyMobileReport) located in “/” directory to “C:\Users\admin\Downloads”.

Out-RsRestCatalogItem -RsItem "/MyMobileReport" -Destination "C:\Users\admin\Downloads" -WebSession $testSession -RestApiVersion v1.0

If you want to download everything under a specific folder, you can run the following command:

Out-RsRestFolderContent -RsFolder "/" -Destination "C:\Users\admin\Downloads" -WebSession $testSession -RestApiVersion v1.0

Note: If you want to download everything under a specific folder including all sub-folders (and items found in them), you should specify -Recurse switch at the end of the command.

Deploying Content via REST API

After you have downloaded all the content from your test server, you can deploy content to your production server in a very similar way. Again, v1.0 of REST API is NOT supported by Microsoft. But there is no other way you can upload Mobile Reports other than using this version of the REST API.

Just like earlier, I highly recommend you to create a WebSession.

$prodSession = New-RsRestSession -ReportPortalUri http://ssrs-prod/reports -RestApiVersion v1.0

Once your WebSession is created, in order to upload a single item, you should run the following command:

Write-RsRestCatalogItem -Path "C:\Users\admin\Downloads\MyMobileReport.rsmobile" -RsFolder "/" -WebSession $prodSession -RestApiVersion v1.0

If you want to upload an entire folder, you should run the following command:

Write-RsRestFolderContent -Path "C:\Users\admin\Downloads" -RsFolder "/" -WebSession $prodSession -RestApiVersion v1.0

Note: If you want to upload everything under a specific folder including all sub-folders (and items in those folders), you should specify -Recurse switch at the end of the command.

If you want to upload items to a specific folder, which does not exist on the Report Server, I recommend using the following command (which uses SOAP API) to create the folder and then to upload the content using the commands above.

New-RsFolder -ReportServerUri http://ssrs-prod/reportserver -FolderName "My Folder" -RsFolder "/"

2) SOAP API

Downloading Content via SOAP API

First I recommend you to create a Web Service Proxy. While this isn’t required, you can reuse the proxy so that you don’t need to specify the Report Server URI for every command you run against your server.

$testProxy = New-RsWebServiceProxy -ReportServerUri http://ssrs-test/reportserver

Next you need to decide where you want to download the content to. In the following example, I am downloading a Paginated Report (called MyReport) located in “/” directory to “C:\Users\admin\Downloads”.

Out-RsCatalogItem -Proxy $testProxy -RsItem "/MyReport" -Destination "C:\Users\admin\Downloads"

If you want to download everything under a specific folder, you can run the following command:

Out-RsFolderContent -Proxy $testProxy -RsFolder "/" -Destination "C:\Users\admin\Downloads"

Note: If you want to download everything under a specific folder including items under any sub-folder, you should specify -Recurse switch at the end of the command.

Uploading Content via SOAP API

After your content has been downloaded, then you want to upload all this content to your production server.

Just like earlier, I highly recommend you to create a Web Service Proxy to your production server.

$prodProxy = New-RsWebServiceProxy -ReportServerUri http://ssrs-prod/reportserver

Once you created the proxy, you can upload items using the following command:

Write-RsCatalogItem -Proxy $prodProxy -Path C:\Users\admin\Downloads\MyReport.rdl -RsFolder "/"

If you want to upload all content founder under a specific folder, you can run the following command:

Write-RsFolderContnet -Proxy $prodProxy -Path C:\Users\admin\Downloads -RsFolder "/"

Note: If you want to upload everything under a specific folder including all sub-folders (and items in those folders), you should specify -Recurse switch at the end of the command.

If you want to upload items to a specific folder, which does not exist on the Report Server, I recommend using the following command (which uses SOAP API) to create the folder and then to upload the content using the commands above.

New-RsFolder -Proxy $prodProxy -FolderName "My Folder" -RsFolder "/"

2 Comments

Add a Comment

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