Export SSRS Report Inventory to CSV files

Today, I’m preparing documentation for pulling DB Instance reports to CSV files for SQL server Instance Migration since we have a change scheduled for tonight, which has reporting services installed, and there’s a request from customer to pull all SSRS reports to CSV files with Fields as SSRSServerName, ReportName, Connection details, Datasource details, DataSourcepath details.

To do this, we can use a different approaches, but I prefer to use a simple PowerShell script that requires only the parameters of the Reporting Services Server Name and the file path to the CSV file where the Reports information will be saved.

I recently went through and searched my old script repository and noticed that I already had this PowerShell script saved. We must appreciate the author for this script.

The PowerShell script can be found below:

$SSRS2012Server = "DESKTOP-02JIB76" #  Here Provide your SSRS Server Name
$ReportPathMatch = "*" # you can also specify certain reports, * for all
$file = "C:\temp\SSRS_Inventory.csv" # Provide the path to create CSV file

#URL to the web service
$SSRSurl = "http://{0}/ReportServer/ReportService2010.asmx" -f $SSRS2012Server
# Get a handle on the service
$SSRS = New-WebServiceProxy -uri $SSRSurl -UseDefaultCredential;
# Get a collection of the reports on the server
$itemlist = $SSRS.ListChildren("/", $true) | Where-Object {$_.TypeName -eq "Report" -and $_.Path -like $ReportPathMatch -and $_.Path -notlike "/Users Folders*"} ;
Write-Host "Reports to review" $itemlist.Count
#Creating a report tracker
$Reports =@()
#Creates a report tracking object
function Add-Reports
param([string]$SSRSServer, [string]$reportPath, [string]$connString, [string]$dataSourceType , [string]$dataSourcePath)
$report = New-Object System.Object
$report | Add-Member -type NoteProperty -name "SSRSServer" -value $SSRSServer
$report | Add-Member -type NoteProperty -name "Report" -value $reportPath
$report | Add-Member -type NoteProperty -name "Connection" -value $connString
$report | Add-Member -type NoteProperty -name "DataSourceType" -value $dataSourceType
$report | Add-Member -type NoteProperty -name "DataSourcePath" -value $dataSourcePath
return $report

$SSRSServer = $SSRS2012Server

#Loop Through Each Report
Foreach($item in $itemlist)
Write-Host "Checking " $item.Path
$dsList = $null #clearing it out explicitly
#Any given report may have multiple data sources, grab the collection
$dsList = $ssrs.GetItemDataSources($item.Path)
#Loop through each DS in the report
for($i=0;$i -le $dsList.Length-1;$i++)
$ds = $dsList[$i];
#If the DS has a reference it would appear to be a shared datasource
if($ds.Item.Reference -ne $null)
$dataSource = $ssrs.GetDataSourceContents($ds.Item.Reference);
$Reports += Add-Reports -SSRSServer $SSRSServer -reportPath $item.Path -connString $dataSource.ConnectString -dataSourceType "Shared" -dataSourcePath $ds.Item.Reference
else #embedded datasource
$dataSource = $ds.Item
$Reports += Add-Reports -SSRSServer $SSRSServer -reportPath $item.Path -connString $dataSource.ConnectString -dataSourceType "Embedded" -dataSourcePath ""
} #for each datasource
Write-Host "Error on " $item.Path $Error[0]
} # Foreach Report
$Reports | Export-Csv $file -Force

The following is the output of the aforesaid script:

All 31 reports are then exported and saved to a CSV file, as shown below:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: