I recently needed to replicate all of the reports on one report server to a different server. I started downloading each report one at a time to a file system folder, moving them to the other machine and then uploading each RDL file. Needless to say, that’s a slow and painful process. Since a new set of PowerShell command lets (Cmdets) were introduced for Reporting Service just a couple of weeks ago, I decided to give them a try and share the results.
Aaron Nelson, long-time Microsoft Data Platform MVP and PowerShell fanatic, has literally been hounding me for the past few weeks to take some time and look at a new PowerShell library and scripts originally developed by Parth Shah from the SSRS product team. Jaime Tarquino also played a big role in automating the build process. It’s the personal commitment of folks at Microsoft like Parth and Jaime that really make a product like SSRS shine.
Aaron can be pretty persistent and I admit it’s ironic that I’ve been too busy finishing up the final editing work on the Wrox SSRS 2016 book and the new edX SSRS course to look at these RS PowerShell CmdLets but I’m glad that I did because they’re an absolute gold mine.
Back in July Aaron asked me to post some questions about what PowerShell support members of the Reporting Services community would like to see. That post is here. We got some good feedback to help the product team prioritize their efforts in this area. Aaron and and Chrissy LeMaire worked closely with the Reporting Services team to improve the commands before they were recently announced, and the team cited their help in this announcement: https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/11/07/community-contributions-to-the-powershell-scripts-for-reporting-services/
Back to my project… I have developed several demo and lab exercise reports for the new edX SSRS course: Analyzing and Visualizing Data with SQL Server Reporting Services. The development environment is an Azure virtual machine that I recently didn’t have access to while travelling and needed to move all the reports to a local VM to continue my work. I needed to make sure that current versions of these dozens of reports are on both servers. Using a couple of new PowerShell commands, this was quite easy. Here’s how it worked:
Step 1: I open the PowerShell ISE console on the server where I need to archive the reports (shown with the script I’ll run in the next few steps)
Step 2: run a command to download and install the rstools library (see line 1):
Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)
Step 3: Line 5 downloads all the reports in the “DAT214x Course” folder on my Azure VM report server to a local file system folder:
Out-RsFolderContent …
Step 4: On the destination server, I also run the command to install the rstools library…
Step 5: After copying the files to a local folder (or you could use a UNC path to access a shared folder), run Write-RsFolderContent to upload all the reports to the same destination folder:
Progress is displayed while the files are uploaded:
With shared data sources in the same places, all of the files are uploaded and working:
This saved me hours of time and provided peace of mind, knowing that I could easily automate large migrations in the future.
Here is the complete list of commands. You can get help, execution details and optional parameter information by prefixing the command with the word “help “:
Backup-RsEncryptionKey
Get-RsCatalogItems
Get-RsDataSource
Get-RsItemReferences
Grant-AccessOnCatalogItem
Grant-AccessToRs
New-RsConfigurationSettingObject
New-RsDataSource
New-RsWebServiceProxy
Out-RsCatalogItem
Out-RsFolderContent
Register-PowerBI
Remove-RsCatalogItem
Restore-RSEncryptionKey
Revoke-AccessOnCatalogItem
Revoke-AccessToRs
Set-RsDataSet
Set-RsDataSource
Set-RsDataSourcePassword
Set-RsEmailSettingsAsBasicAuth
Set-RsEmailSettingsAsNoAuth
Set-RsEmailSettingsAsNTLMAuth
Set-RsSharedDataSource
Write-RsCatalogItem
Write-RsFolderContent
Some additional notes (from Aaron)…
You can download these commands into your PowerShell environment very easily by running this simple command:
Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)
You need to be running PowerShell as Administrator in order to do this.
My favorite command among them is this one which will upload an entire folder worth of Reports & Datasets to an SSRS server for you. Here’s an example using Jamie Thomson’s SSIS Reporting Pack (attached):
Write-RsFolderContent -ReportServerUri ‘http://localhost/ReportServer_SQL2016’ -Path ‘C:\SSIS Reporting Pack\Reports’ -Destination /SSISReporting
You can get the full list of commands by running this:
Get-Command -Module ReportingServicesTools
If you have access to multiple report servers, you only need to run PowerShell commands on one machine.
Folder recursion is supported if you have nested folders.
I think you’ll find these commands can help speed up your Development Lifecycle.
Thanks again to Parth, Jaime, Aaron, Chrissy, Riccardo and the rest of the product team for making this possible!
