mssqltips logo

Accessing SQL Server Reporting Services Data Using PowerShell

By:   |   Updated: 2016-05-18   |   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.

SSRS Configuration Manager

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.

Access SQL Server Reporting Servvices via PowerShell

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.

SQL Server Reporting Services Home Directory

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.

SQL Server Reporting Services Home Directory Output via PowerShell

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.

SSRS Folders, Subfolders, Datasources, Datasets and Reports for a given folder

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.

SSRS Folders, Subfolders, Datasources, Datasets and Reports for a given folder via PowerShell

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.


Last Updated: 2016-05-18


get scripts

next tip button



About the author
MSSQLTips author Nat Sundar Nat Sundar is working as an independent SQL BI consultant in the UK with a Bachelors Degree in Engineering.

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.





Thursday, January 12, 2017 - 1:14:22 AM - Dorababu Back To Top

Thanks for your reply it seems I am passing different url http://localhost:85/Reports instead of this http://localhost:85/Reportserver/ReportService2016.asmx it is working now 

 


Wednesday, January 11, 2017 - 9:26:29 AM - Nat Sundar Back To Top

 

Are you able to access the Reporting services from the Reporting Services Manager ?

If so can you share the reporting service web service URL ?


Wednesday, January 11, 2017 - 3:33:15 AM - Dorababu Back To Top

 I am getting the following exception The HTML document does not contain Web service discovery information.

 



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