Wayne Sheffield (blog | @DBAWayne) invited me to a party, and anyone that knows me, knows I love a good party. This month’s party is all about how we have integrated Posh into our SQL lives. Thank you Wayne for hosting this month’s T-SQL Tuesdays #39 Can you shell what the PoSH is Cooking?

I have to admit that I am relatively new to PowerShell and all its functionality, however I have quickly learned the power of such a tool.

Before this party started (T-SQL Tuesday #39), I was tasked with creating a process to upload reports to Reporting Services. This process had to be repeatable, automatic, and configurable. My initial suggestion was to just upload each report to the necessary folders, however that still includes the human factor, so repeat-ability is out. After digging around on the inter-webs I came across examples of SSRS report push using a Web Services Proxy. After a few modifications, I was able to use a couple Cmdlets and the Web Services method to upload reports. In this blog post I am going to show how to use Powershell v2.0 Cmdlets and Proxy calls for uploading SSRS reports to a Reporting Services Web Server.

The following example demonstrates how to upload a report (RDL file) and load the report to appropriate folder using the Reporting Services Web Services.

$SSRSService = "http://localhost/ReportServer/ReportService2005.asmx"
$SSRSReport = "C:\Dev\SSRSReport.rdl"
$SSRSNameWithoutExtension = [System.IO.Path]::GetFileNameWithoutExtension($SSRSReport);
$SSRSPath = "/"
 
Write-Output "Reporting Services Server: $ReportingServices"
$SSRSProxyObject = New-WebServiceProxy -Uri $SSRSService -UseDefaultCredential 
 
Write-Output "Converting $SSRSReport to a byte array"
$ByteArray = Get-Content $SSRSReport -Encoding byte     
 
Write-Output "SSRS Report Name: $SSRSNameWithoutExtension "
$SSRSProxyObject.CreateReport($SSRSNameWithoutExtension, $SSRSPath, $true, $ByteArray, $null)

The key PowerShell statements are:

New-WebServiceProxy (Cmdlet)

The New-WebServiceProxy Cmdlet is used for creating a web services proxy object. We will use this to create a proxy object from our Reporting Services Servers Web Services.
New-WebServiceProxy Cmdlet
Example:

$SSRSProxyObject = New-WebServiceProxy -Uri $SSRSService -UseDefaultCredential

Get-Content (Cmdlet)

We will use the Get-Content Cmdlet to get the byte array representation of the RDL file.
Get-Content for FileSystem Cmdlet
Example:

$ByteArray = Get-Content $SSRSReport -Encoding byte

CreateReport (SSRS Web-Service Call)

After we have our Reporting Services Proxy object, we can use it to call a CreateReport() method, which uploads the report to your selected $SSRSPath.
ReportingService2005.CreateReport Method
Example:

$SSRSProxyObject.CreateReport($SSRSNameWithoutExtension, $SSRSPath, $true, $ByteArray, $null)