T-SQL Tuesday #39 – Can you shell what the PoSH is Cooking?


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

$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

$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

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

BI Templates

Templates in BIDS or SSDT are a great time-saving feature for both SSIS and SSRS. They also promote consistency across all developed reports and integration services packages. Finding the correct path to store templates can be a challenge, which is what I hope this blog will illustrate. Below I listed a quick reference where to store your templates.

Templates Paths for SQL Server 2005

%Program Files%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\[Template Type]

Templates Paths for SQL Server 2008 and 2008 R2

%Program Files%\ Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\[Template Type]

Templates Paths for SQL Server 2012

%Program Files%\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\[Template Type]

SSIS = DataTransformationProject
SSRS = ReportProject


  • These paths are based on default installs, you may need to adjust according to your install location.
  • The %Program Files% will be either Program Files or Program Files (x86), depending on if you are using a 64bit architecture.
  • You will need to replace the [Template Type] with DataTransformationProject for SSIS and ReportProject for SSRS