mssqltips logo

PowerShell Commands for SQL Server Reporting Services

By:   |   Updated: 2017-03-10   |   Comments (13)   |   Related: > Reporting Services Administration

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 Updated: 2017-03-10


get scripts

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
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, July 10, 2019 - 7:38:06 AM - Sam Back To Top

I started with downloading the .rdl files to a folder using step 1 below, followed by the steps you listed above. I was confused about the path info you showed. Great post, thank you.

#Step1 - Download the source .rdl files 

Out-RsFolderContent -ReportServerUri 'http://sourceserver/ReportServer' -RsFolder "/Folder Name to copy" -Destination \\SharedServer\U

## Step 2: Created the parent folder in the destination server, ran below on Dest Server  

New-RsFolder -ReportServerUri http://DestServer/ReportServer_Name/ -Path / -Name "Dest Folder Name" -Verbose

## Step 3: copied the .RDL files which were loaded in Step1 to the Dest server. Ran below on Dest Server 

Write-RsFolderContent -ReportServerUri http://DestServer/ReportServer_Name/-Path '\\SharedServer\U' -Destination "/Dest Folder Name" -Verbose  


Tuesday, July 09, 2019 - 9:27:05 PM - Sam Back To Top

Hi Aaron,

Can you please tell me how to get -path value for the source SSRS?! You are saying the path is the folder with all reports, can you please specify where to get that value? 


Tuesday, July 09, 2019 - 9:24:34 PM - Sam Back To Top

Greetings,

I am following the steps you listed above to migrate a folder from a source SSRS server to another one, and I am trying to understand where to get the path information. You stated that -path is the folder with all reports, what I get path subfolder from the category table and use that, I get an error about the path is not a folder!!! I am not sure which path we are talking about? I appreciate if you show where to get that path information for the source SSRS. 


Monday, May 27, 2019 - 2:49:29 PM - Andy Mishechkin Back To Top

Hi, Aaron!

I have made SSRS reports deployment, using your tools, and I want to point to some issues, which took a place in deployment process:

1. I couldn't find special function for checkin the SSRS folder. I  had to use ([Microsoft.ReportingServicesTools.ConnectionHost]::Proxy).ListChildren("/",$false) instead.

2. Would be good to have the special function for testing the DataSource.

3. I couldn't create the shared data source and to select common report folder datasource in the report using Get-RsItemDataSource \ Set-RsItemDataSource. Only a custom data source was created (see the comments below) . But perhaps I have made something wrong. May you explain - how to create shared data source in report and assign it to report folder datasource ?

Also, where can I find [Microsoft.ReportingServicesTools.ConnectionHost] namespace and Proxy class reference ? This will be nice to have a detailed description of this class properties and methods.


Thursday, May 23, 2019 - 4:40:20 PM - Andy Mishechkin Back To Top

About previous post - nevermind

I took a look to Set-RsItemDataSource source code and have made the next decision:

$FolderDataSource = Get-RsDataSource -ReportServerUri 'http://SqlSrv/ReportServer' -Path '/ReportFolder/Folder-DataSource'

$ReportDataSource = Get-RsItemDataSource -ReportServerUri 'http://SqlSrv/ReportServer' -RsItem '/ReportFolder/Report1'

$ReportDataSource.Item = $FolderDataSource

Set-RsItemDataSource -ReportServerUri 'http://SqlSrv/ReportServer' -RsItem '/ReportFolder/Report1' -DataSource $ReportDataSource


Thursday, May 23, 2019 - 2:39:52 PM - Andy Mishechkin Back To Top

Hello!

--That's a great idea! I have passed your idea along and will report back when I have news
Thank you!

Also, may you explain how to set shared data source in report ?

I have a data source in reports folder and I try to get this object:
$FolderDataSource = Get-RsDataSource -ReportServerUri 'http://SqlSrv/ReportServer' -Path '/ReportFolder/Folder-DataSource'

And try to set in in report:
Set-RsItemDataSource -ReportServerUri 'http://SqlSrv/ReportServer' -RsItem '/ReportFolder/Report1' -DataSource $FolderDataSource

And I have got the error:
Invalid data source specified: Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1tServer_ReportService2010_asmx.DataSourceDefinition!

What should I do for correct setting the shared data source in report ?


Thursday, May 23, 2019 - 9:32:02 AM - Aaron Nelson Back To Top

@Andy

That's a great idea! I have passed your idea along and will report back when I have news. Thanks for helping make the ReportingServicesTools module even better :)


Thursday, May 23, 2019 - 7:53:40 AM - Andy Mishechkin Back To Top

Hello!

Thank you for these really usefull tools.

I have created new DataSource using the New-RsDataSource function. RS DataSource has a button 'Test Connection' inside. 

May you give advice - can I test DataSource through PowerShell like using 'Test Connection' button ?


Saturday, April 13, 2019 - 9:00:57 AM - Jim Smith Back To Top

Aaron,

Thanks for this well-written and very very helpful article.  

Cheers,

Jim


Wednesday, May 16, 2018 - 1:38:54 PM - BISWA Mahapatra Back To Top

 This is a great article. I'm truly being helped with GUI presentations.

I'm stumbling into one issue that at some reports when i provide the sql server it works and many places/reports the value generates into error stating "The 'version_string' parameter is missing a value"

 

main page accepts the server name and the same does not work for many other reports...am i missing anything?


Friday, March 23, 2018 - 6:48:15 AM - Navaneethakrishnan Thangaraj Back To Top

 

 Hi Aaron,

Great post, I am using this for SSRS Report publishing and working fine. Thanks for your help.

With the same PowerShell code, I have tried to publish the RDL reports to Power BI Report Server (On Prime) and able to publish the reports.

But while publishing the PBIX files (Power BI Report files) to Power BI Report server, getting below error on the same code/module.

 

 

Exception calling "CreateCatalogItem" with "7" argument(s): "System.Web.Services.Protocols.SoapException: The value of parameter 'ItemType' is not valid. 

---> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidParameterException: The value of parameter 'ItemType' is not valid.

   at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Prope

rty[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)

   at Microsoft.ReportingServices.WebServer.ReportingService2010.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Propert

y[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)".Exception.Message

 

I have altered the "Write-RsCatalogItem.ps1" file function to allow the PowerBIReport as Item type, but it is not allowed in "CreateCatalogItem" method.

  if ($itemType -ne "Report" -and

$itemType -ne "DataSource" -and

$itemType -ne "DataSet" -and 

$itemType -ne "PowerBIReport")

{ }

 

$Proxy.CreateCatalogItem($itemType, $itemName, $RsFolder, $Overwrite, $bytes, $null, [ref]$warnings) | Out-Null

 

 

Can you please help on this?

 

Thanks,

Navaneethakrishnan Thangaraj

 

 


Tuesday, August 29, 2017 - 5:43:45 PM - Jonathan Back To Top

This doesn't seem to support RDS data sources.


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



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools