In this article I will show how you can use Powershell to download Reporting Services reports in bulk, how to upload reports and set their shared datasources in bulk, and how to query the report server’s contents such as listing the locations of all linked reports that reference a particular report.

Introduction

While the SQL Server Reporting Services (SSRS) web interface is fine for managing individual reports, in general it lacks the functionality to perform these operations in bulk. Also, some information simply cannot be obtained through the user interface, such as which linked reports reference a given report.

Luckily, SSRS provides a soap webservice, which allows SSRS to be managed programmatically, for example, through Powershell.

Connecting to the Report Server webservice

For invoking SOAP webservices, Powershell provides a very powerful cmdlet called New-WebServiceProxy which basically uses the services’ wsdl to generate a strongly-typed webservice client on the fly. For example, to connect to the local report server instance using the credentials of the user that’s running the script:

$reportServerUri = "http://localhost/reportserver/ReportService2010.asmx?wsdl"
$rs = New-WebServiceProxy -Uri $reportServerUri -UseDefaultCredential 
                          -Namespace "SSRS"

There are a couple of things to note here:

• The url references the /reportserver, not the web UI which is on /reports.
• Even though the url contains 2010, it’s still the latest version (at least for SQL Server 2014)
• By specifying -Namespace "SSRS", we tell it to place all generated types from the wsdl into a .NET namespace called SSRS. This is useful when we need to instantiate some of these types ourselves – we can then “new” them up by prefixing the wsdl type with this namespace, i.e. “New-Object SSRS.DataSourceReference“

Querying for (linked) reports

Once the proxy has been created, we’re free to query at will – provided that your credentials are authorized to do so, of course:

# List everything(!) on the Report Server, recursively
$catalogItems = $rs.ListChildren("/", $true)
$catalogItems

# List all Linked Reports, together with the path of the Report it refers to.
$linkedReports = $rs.ListChildren("/", $true) | Where-Object { $_.TypeName -eq "LinkedReport" }
$results = $linkedReports | Foreach-Object {
    $linkPath = $rs.GetItemLink($_.Path)

    $result = new-object PSObject -Property @{ LinkName = $_.Name; LinkPath = $_.Path; ReportPath = $linkPath }
    $result
}
$results

# List all Linked Reports that refer to reports in a specific folder:
$results | Where-Object { $_.ReportPath -like "/Reports/MyDeparment/*" }

Downloading multiple reports

Quickly downloading all reports from a particular folder as .rdl files is now a piece of cake as well:

# Download all Reports from a specific folder to .rdl files in the current 
# directory.
$sourceFolderPath = "/Reports/MyDeparment"
$items = $rs.ListChildren($sourceFolderPath, $false)
$items | Where-Object { $_.TypeName -eq "Report" } | Foreach-Object {
    $filename = ("{0}.rdl" -f $_.Name)

    Write-Output ("Downloading ""{0}""..." -f $_.Path)
    $bytes = $rs.GetItemDefinition($_.Path)
    [System.IO.File]::WriteAllBytes("$pwd\$filename", $bytes)
}

Uploading multiple reports

However, the power of Powershell (ha!) really starts to show when you want to upload multiple reports at once – and for good measure, we’ll also set the shared data source for each Report to an (already existing) datasource.

# Upload all .rdl files in the current directory to a specific folder, and 
# set their datasource references to the same shared datasource (should 
# already be deployed).
$targetFolderPath = "/Reports/MyNewReports"
$targetDatasourceRef = "/Data Sources/mySharedDataSource"
$warnings = $null

Get-ChildItem *.rdl | Foreach-Object {
    $reportName = [System.IO.Path]::GetFileNameWithoutExtension($_.Name)
    $bytes = [System.IO.File]::ReadAllBytes($_.FullName)

    Write-Output "Uploading report ""$reportName"" to ""$targetFolderPath""..."
    $report = $rs.CreateCatalogItem(
        "Report",         # Catalog item type
        $reportName,      # Report name
        $targetFolderPath,# Destination folder
        $true,            # Overwrite report if it exists?
        $bytes,           # .rdl file contents
        $null,            # Properties to set.
        [ref]$warnings)   # Warnings that occured while uploading.

    $warnings | ForEach-Object {
        Write-Output ("Warning: {0}" -f $_.Message)
    }

    # Get the (first) *design-time* name of the data sources that the 
    # uploaded report references. Note that this might be different from 
    # the name of the datasource as it is deployed on the report server!
    $referencedDataSourceName = (@($rs.GetItemReferences($report.Path, "DataSource")))[0].Name

    # Change the datasource for the report to $targetDatasourceRef
    # Note that we can access the types such as DataSource with the prefix 
    # "SSRS" only because we specified that as our namespace when we 
    # created the proxy with New-WebServiceProxy.
    $dataSource = New-Object SSRS.DataSource
    $dataSource.Name = $referencedDataSourceName      # Name as used when designing the Report
    $dataSource.Item = New-Object SSRS.DataSourceReference
    $dataSource.Item.Reference = $targetDatasourceRef # Path to the shared data source as it is deployed here.
    $rs.SetItemDataSources($report.Path, [SSRS.DataSource[]]$dataSource)
}

Summary

Obviously, there’s a lot more that you can accomplish using Reporting Service’s webservice. You can find the complete list of operations here, and categorized by usage here.

Cheers!

Share this