By: Nat Sundar | Comments (3) | Related: > Reporting Services Administration
Problem
I heard that you can access SQL Server Reporting Services data using PowerShell command line scripts. Can you show me how this can be done?
Solution
There are multiple ways to work with SQL Server Reporting Services (SSRS) reports and one of the preferred ways is to use a PowerShell script. There are numerous advantages to using a PowerShell script. Almost all of the Continuous Integration (CI) tools support PowerShell scripts, so it is easy to develop and integrate the script within CI for build and deployment. PowerShell supports both native and SharePoint mode installation of SQL Server Reporting Services.
PowerShell
It is expected that you have PowerShell version 2.0 or later and you have installed SQLPS module (which comes with SQL server 2012).
Refresh your knowledge on PowerShell using these tips.
SSRS Configuration Manager
To access the SSRS reports we need the SSRS Report Server Web Service URL, not the reporting manager URL. This can be identified in the SSRS Configuration Manager as shown in the below picture.
You can validate the URL by accessing the URL using a browser. If you experience any issues, then you may need to validate your permissions for the server.
After adding the WSDL contract, the actual URL will be like the one given below (replace localhost with your SSRS instance name).
http://localhost/ReportServer_SQL2012/ReportService2010.asmx?wsdl
The name "ReportServer_SQL2012" refers to the virtual directory as mentioned in the SSRS Configuration Manager.
Access the SSRS Instance using PowerShell
In this tip, we will be using the Report Server web service to access SSRS using PowerShell.
The PowerShell V 2.0 has a cmdlet New-WebServiceProxy. This cmdlet uses the Report server WSDL to create a typed Web Service client.
This cmd-let lets you create a proxy for the given SSRS server instance. This proxy will have the details about the server properties and the details of all its contents. Using this proxy, we will be able to deploy the reports.
In the below mentioned PowerShell script, a variable has been declared and assigned to the value of the SSRS Web Service URL. A proxy has been created by passing the Web Service URL and the default credentials. Once the proxy has been successfully created, then the script will print the details of the proxy in the result window.
Let's try the script below on the PowerShell editor.
$ReportServerUri = "http://localhost/ReportServer_SQL2012/ReportService2010.asmx?wsdl"
try{
#Create Proxy
$global:proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential -ErrorAction Stop;
If ($proxy -ne $null)
{
echo $global:proxy.ToString()
}
}
catch {
$valProxyError = $_.Exception.Message;
echo $_.Exception.Message;
}
You will get this message (as mentioned in the image below) after the successful execution. If you see any error message, then it is worth validating the permissions for the current user.
Print all Folders in the Home Directory
As a next step, let’s try to print all the folder details in the Home Directory.
The below mentioned SSRS instance has four folders and we will make use of the PowerShell script to print the details.
As we have already created a proxy for the SSRS instance, the ListChildren method will help us to get the details of the sub folders.
The ListChildren accepts two parameters. The fist parameter
denotes the Home directory (/) and the second parameter is a Boolean flag to
highlight for recursive search.
The ListChildren will return the details about the Folders,
Subfolders, Datasources, Datasets and Reports for the given directory. The
details can be printed by accessing the object.
If you are testing the below mentioned script against a Production server,
this may take a while to run (because of the recursive option).
$ReportServerUri = "http://localhost/ReportServer_SQL2012/ReportService2010.asmx?wsdl"
try{
#Create Proxy
$global:proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential -ErrorAction Stop;
If ($proxy -ne $null)
{
echo $global:proxy.ToString()
$SSRSAssets = $proxy.ListChildren("/",$false)
echo $SSRSAssets
}
}
catch {
$valProxyError = $_.Exception.Message;
echo $_.Exception.Message;
}
The execution result of the PowerShell script can be seen below.
Print Specific SSRS Folder Contents
Now we can leverage PowerShell to print all the details about the Folders, Subfolders, Datasources, Datasets and Reports for a given folder. The recursive option can also be set to print details recursively.
In the given SSRS report server instance, the "AdventureWorks" folder contains the DataSources, Datasets and Reports. Lets try to print the name of all files in this folder recursively.
Let's reuse the same script with some enhancements.
An additional variable FolderName has been declared and assigned the value as “/AdventureWorks”. Now let us pass the folder name and the recursive flag as parameters to the ListChildren function.
$ReportServerUri = "http://localhost/ReportServer_SQL2012/ReportService2010.asmx?wsdl"
$FolderName = "/AdventureWorks"
try{
#Create Proxy
$global:proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential -ErrorAction Stop;
If ($proxy -ne $null)
{
echo $global:proxy.ToString()
$SSRSAssets = $proxy.ListChildren($FolderName,$true)
echo $SSRSAssets
}
}
catch {
$valProxyError = $_.Exception.Message;
echo $_.Exception.Message;
}
The script will print the details about the Reports, Datasets and DataSources recursively as mentioned below.
Summary
As you can see, the SQL Server Reporting Service data can be accessed using a PowerShell script, so take advantage of PowerShell where appropriate.
Next Steps
- Stay tuned to learn about deploying Data Sources and Datasets in the next tip.
- Read about SSRS and PowerShell scripts here.
- Read about PowerShell cmdlets for SSRS SharePoint mode here.
- Read about Web Service proxy here.
- Read about SSRS web service here.
- Read more SSRS tips.
- Read more PowerShell tips.
About the author
Nat Sundar is working as an independent SQL BI consultant in the UK with a Bachelors Degree in Engineering.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
