PowerShell Commands for SQL Server Reporting Services

By:   |   Comments (20)   |   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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, April 5, 2024 - 7:50:31 PM - Sam Back To Top (92156)
Please I need some help coming up with a script that upload, download and delete from ssrs report server.

Friday, March 15, 2024 - 7:04:34 AM - kaviyarasan Back To Top (92073)
Is it possible to install, configure, deploying dashboard and update data source to power BI report server.

Thursday, August 18, 2022 - 1:01:07 AM - Gangst Back To Top (90387)
You should also address the many configuration issues that are associated with this. I don't think these steps will work for most people.

Thursday, June 24, 2021 - 11:20:52 PM - Jon Back To Top (88892)
I used your scripts to download all my reports and shared datasets from one folder and upload them to another. That saved a bunch of time but unfortunately now all my reports can't find their shared datasets and nothing can find the shared data source even though it is named the same in both folders. "We can no longer find this dataset. If it was moved, choose it from its new location."

Tuesday, March 9, 2021 - 8:22:28 AM - Aaron Nelson Back To Top (88366)
@Michael Could you expand on this issue a little more? Are you looking to move the reports you mentioned to a different RS folder, or are you trying to rename the reports?
(or maybe both?)

Tuesday, March 9, 2021 - 7:42:52 AM - Michael Richard Edwards Back To Top (88365)
The Microsoft Powershell commands seem to miss the MoveItem method that is avaliable in DotNet (see https://docs.microsoft.com/en-us/dotnet/api/reportservice2010.reportingservice2010.moveitem)

Is there a way to do this with Powershell? I have numerous reports not executed for many months and want to rename them with a TOBEDELETED suffix and come back in 6 months and use PS to delete them at that stage. Thanks

Wednesday, April 15, 2020 - 2:54:20 PM - Lee Eyre Back To Top (85383)

This works great to localhost but when trying to upload to my production server I get the error...

Failed to establish proxy connection to http://serveripaddress/ReportServer/ReportService2010.asmx : There was an error

downloading 'http://serveripaddress/ReportServer/ReportService2010.asmx'.

Any ideas?

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

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 9, 2019 - 9:27:05 PM - Sam Back To Top (81719)

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 9, 2019 - 9:24:34 PM - Sam Back To Top (81718)

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 (81224)

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 (80175)

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 (80173)

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 (80168)

@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 (80167)

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 (79550)

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 (75957)

 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 (75507)

 

 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 (65588)

This doesn't seem to support RDS data sources.

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

 

  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














get free sql tips
agree to terms