Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

PowerShell Commands for SQL Server Reporting Services


By:   |   Read Comments (1)   |   Related Tips: > Reporting Services Administration


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


Problem

You need to deploy a large number of reports to an instance of SQL Server Reporting Services (SSRS), be it a new install of SSRS or you just want to automate deploying updates of reports you have developed. The old command line tool for SSRS (RS.exe) was a pain to learn, let alone use.  In this tip we cover new PowerShell commands that can simplify this process.

Solution

Back in November 2016 the SSRS team announced the availability of 28 new PowerShell commands for working with SSRS (in Native Mode) available in the PowerShell Gallery and in GitHub. These commands use the SSRS API so a lot of the commands will work with SSRS instances as old as SQL Server 2008 R2, however some of the commands like Register-PowerBI, will only work with a SSRS 2016+ instance.

To download and install these tools from the PowerShell gallery is very simple, just open up PowerShell running in Administrator mode and run this command: Install-Module -Name ReportingServicesTools. If you are running an earlier version of PowerShell (4.0-) you can download the module from the SSRS team's GitHub site just run this command: Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools).

Install-Module -Name ReportingServicesTools            
            
Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)

Once installed, you can find a full list of the commands available by running:

Get-Command -Module ReportingServicesTools

The commands can help you administer SSRS, but more importantly they can help you speed up your development lifecycle!

commands can help you administer SSRS

Deploying an Entire Folder Report Items with Write-RsFolderContent

My favorite command out of the entire bunch is Write-RsFolderContent which will let you take an entire folder full of SSRS Reports, Datasets and Data Sources on your machine and deploy them to an SSRS server. Sound too easy? Let's have a look!

Tiger ( blog | twitter ) is the customer success team at Microsoft that focuses on learning customer workloads running on SQL Server, providing guidance to use SQL Server to solve their business challenges and integrate that feedback to enhance the product. The MSSQL Tiger Team has made their SQL Server Performance Dashboard Reports available on GitHub and it is a great example for using this command. You can download the zip file with all of the reports by clicking here, and then unzip them. For this example, I have unzipped the files in a folder called 'MSSQLTips' at the root of my C:\ drive.

Deploying an Entire Folder Report Items with Write-RsFolderContent

Now, instead of deploying all the reports in that folder to the root of my SSRS instance, I'd like to create a folder at the root, and place the reports under that folder. To do that, we'll use the New-RsFolder command.

            
New-RsFolder -ReportServerUri http://localhost/ReportServer -Path / -Name MSSQLTips -Verbose

After running that command, you should have an empty folder like below.

After running New-RSFolder, you should have an empty folder like below

Note: The path to my SSRS instance will be slightly different from the demo code since I am using a named instance of SSRS.

Once my folder is created in SSRS, I'm now ready to deploy all 22 reports that come with the SQL Server Performance Dashboard. For that we'll use Write-RsFolderContent and we need to pass it 3 pieces of information to the following parameters:

  • -ReportServerUri (the location of our SSRS instance),
  • -Path (the folder with all the reports),
  • -Destination (the name of the SSRS folder we want the reports placed under).
Write-RsFolderContent -ReportServerUri http://localhost/ReportServer -Path "C:\MSSQLTips\SQL Server Performance Dashboard\" -Destination /MSSQLTips -Verbose

Write-RsFolderContent and we need to pass it 3 pieces of information

By default, the two commands we have used do not output any information unless an error occurs; to get the output in the screenshot above I added the -Verbose parameter to the end of the commands.

Once the command is finished, we refresh our browser and see that all our reports have deployed.

Once the command is finished, we refresh our browser and see that all our reports have deployed

Alternatively, you can use the Get-RsFolderContent command to see that the report objects have been deployed.

Get-RsFolderContent -ReportServerUri http://localhost/ReportServer -Path /MSSQLTips |             
Format-Table -AutoSize

use the Get-RsFolderContent command to see that the report objects have been deployed

I chose this particular project because you can get the reports running without having to setup and configure a data source after the reports are deployed. Just deploy the "Setup.SQL" file to any servers that you want to run the reports against, and you'll be ready to run the reports. Start at the performance_dashboard_main page, enter the name of the instance you've run the "Setup.SQL" file on, and you'll be able to see a report like the one below.

enter the name of the instance you've run the Setup.SQL file on, and you'll be able to see a report

Quick Aside: One of my favorite reports in this package is the database_storage_report. Have a look at what this report looks like when I run it on my machine, you might like it too:

database storage report

Deploying Individual Report Items with Write-RsCatalogItem

When you need to deploy just a single Report, Dataset, or DataSource you can use the Write-RsCatalogItem command. Here I will deploy the performance_dashboard_main.rdl file to the root of my SSRS server.

Write-RsCatalogItem -ReportServerUri http://localhost/ReportServer -Path "C:\MSSQLTips\SQL Server Performance Dashboard\performance_dashboard_main.rdl" -Destination /

While the performance_dashboard_main report will work if you launch it from there, since we deployed the rest of the SQL Server Performance Dashboard reports under the MSSQLTips folder, clicking on any links in this copy of the report will result in an error. To keep from confusing ourselves, we'll go ahead and remove that report using the Remove-RsCatalogItem command.

Remove-RsCatalogItem -ReportServerUri http://localhost/ReportServer -Path /performance_dashboard_main

Updating Multiple Reports, Datasets, and DataSources with Write-RsCatalogItem

For times when you need to update multiple Reports, Datasets, and/or DataSources, but not the entire folder of those items, you can use the Write-RsCatalogItem command to accomplish this as well. In this scenario, let's say that I had updated all of the reports that have the word 'wait' in them, and I want to upload just those to my SSRS server to test them. I don't want to have to go and remove the old versions of those reports before I deploy the new versions, so I will use the -OverWrite parameter.

You have a couple options of how you could select the multiple files you want to upload to your SSRS instance, some of the most common are:

  • A simple comma separated list
  • Pipeline in from Out-GridView
  • And simply pipelining files into the -Path parameter

We'll start with my favorite, Out-GridView.

Out-GridView

The Out-GridView cmdlet allows you to present the results from PowerShell in a sortable and filterable grid, sort of like the 'Out to Grid' results you're used to in SQL Server Management Studio (SSMS), but better! When you add the -PassThru parameter, it allows you to multi-select rows in the grid, and when you click the 'OK' button it will send only the rows you selected down the pipeline to the next command in the sequence to be processed.

Dir 'C:\MSSQLTips\SQL Server Performance Dashboard\' |            
Out-GridView -PassThru |            
Write-RsCatalogItem -ReportServerUri http://localhost/ReportServer -Destination /MSSQLTips -OverWrite

The Out-GridView cmdlet allows you to present the results from PowerShell in a sortable and filterable grid

Comma Separated List

For the comma separated list example, I'm going to take two reports that I want to deploy, historical_io and historical_waits, and place them into a variable named $Reports first. Behind the scenes, PowerShell is going to automatically turn what seems like two simple strings into an array for me. Then I will pass that $Reports variable to the -Path parameter.

CD 'C:\MSSQLTips\SQL Server Performance Dashboard\'            
            
$Reports = 'historical_io.rdl', 'historical_waits.rdl'            
Write-RsCatalogItem -ReportServerUri http://localhost/ReportServer -Path $Reports -Destination /MSSQLTips -OverWrite

Simple Pipelining

At first glance this 'simple pipelining' approach might not seem much different than using the Write-RsFolderContent command that we discussed before, but there are some important differences here that I will call out. The Write-RsFolderContent command is tailor-made for being pointed at a folder of reports that you're developing, and uploading to the folder on SSRS you specify only the .rdl, .rsds and .rsd files, it knows to skip everything else. The Write-RsCatalogItem command on the other hand, will allow you to send it any type of file, but it will error out if it's not an .rdl, .rsds, or .rsd file.

In the example below, I am going to filter the list of .rdl files in the directory, and only have it send the reports that begin with the word "wait" to the Write-RsCatalogItem command. This can help me keep from deploying reports that haven't been changed.

Dir 'C:\MSSQLTips\SQL Server Performance Dashboard\' -Filter wait*.Rdl |            
Write-RsCatalogItem -ReportServerUri http://localhost/ReportServer -Destination /MSSQLTips -OverWrite

filter the list of .rdl files in the directory, and only have it send the reports that begin with the word wait to the Write-RsCatalogItem command

Another way I could approach this is to pipe my list of files to the Where[-Object] cmdlet and have it only send reports that have been modified since the beginning of the year to the Write-RsCatalogItem command. This could come in handy in times where I'm not sure if I had deployed all of the reports I had touched yet, and wanted to make sure they were all deployed before I started my testing.

Dir 'C:\MSSQLTips\SQL Server Performance Dashboard\' -Filter *.Rdl |             
WHERE LastWriteTime -GT 2017-01-01 |            
Write-RsCatalogItem -ReportServerUri http://localhost/ReportServer -Destination /MSSQLTips -OverWrite 

pipe my list of files to the Where[-Object] cmdlet and have it only send reports that have been modified since the beginning of the year to the Write-RsCatalogItem command
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Aaron Nelson Aaron Nelson is a Senior SQL Server Architect with over 10 years experience in architecture, BI, development and tuning.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, August 11, 2017 - 9:16:02 AM - Mark Back To Top

 

  Hi Aaron,

 

 

 Hope all is well with you and yours.

 

Thanks for the info above but I have another question for you. I have an issue at work where we are currently using custom authentication that has been in place for some time now and we are looking to move to Kerberos/ ntlm. The issue is that we understand that we will not be able to use the existing database and do an in-place upgrade so we will need to extract the reports etc. and import them into a new server.

 The problem is with custom authentication we are not able to just login to the old SSRS environment and use the tools like ReportSync or scripts like the famed ssrs_migration.rss script. The custom authentication goes to our home grown SSO portal that was written over 10 years ago. 

 How can anyone extract the data from SSRS 2008 r2 so it may be imported in to an SSRS 2016 environment considering custom authentication? We have had cases opened with Microsoft on this issue and after thousands of dollars and tons of hours we have not gotten anywhere.

HELP....

 

Regards,

 

Mark


Learn more about SQL Server tools