Export SSRS Report Inventory to CSV files

Today when I am preparing a documentation i.e., pulling DB Instance reports which includes Databases Info, , Configurations, security, linked servers, settings ..e.t.c., to CSV files for SQL server Instance Migration which is scheduled for tonight ,which has reporting services installed and there is a request from customer to pull all SSRS reports to CSV files with Fields as SSRSServerName, ReportName, Connection details, Datasource details, DataSourcepath details

To achieve this we have methods to do but I wanted to do this using PowerShell script which is simple where we need to provide parameters of Reporting Services Server Name and provide file path where CSV file has to be saved.

I have just gone through and searched my old script repository which I already has this PowerShell script saved , We need to thank the Author for this script .

Please find the PowerShell script below :

CLS
$SSRS2012Server = “DESKTOP-02JIB76” # Provide your SSRS Server
$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
try
{
$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
}
catch{
Write-Host “Error on ” $item.Path $Error[0]
$Error.Clear();
}
} # Foreach Report
$Reports | Export-Csv $file -Force

Output from above script will get like below :

And the above all 31 Reports will be exported to CSV file where File path has given like below :

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.