Home > Microsoft SQL Server, Powershell > [Powershell] How to Install/deploy SSRS (rdl files) using Powershell

[Powershell] How to Install/deploy SSRS (rdl files) using Powershell


Since Powershell v2.0 introduce the new cmdlet New-WebServiceProxy, it’s no longer needed to generate a class library to deploy/undeploy SSRS file (RDL), below are two powershell scripts Install-SSRSRDL and Uninstall-SSRSRDL that can be useful when doing an automated deployment or silent installation of rdl files to Reporting Services.

Install SSRS RDL Script:


<#
.SYNOPSIS
	Installs an RDL file to SQL Reporting Server using Web Service

.DESCRIPTION
	Installs an RDL file to SQL Reporting Server using Web Service

.NOTES
	File Name: Install-SSRSRDL.ps1
	Author: Randy Aldrich Paulo
	Prerequisite: SSRS 2008, Powershell 2.0

.PARAMETER reportName
	Name of report wherein the rdl file will be save as in Report Server.
	If this is not specified it will get the name from the file (rdl) exluding the file extension.

.PARAMETER force
	If force is specified it will create the report folder if not existing
	and overwrites the report if existing.

.EXAMPLE
	Install-SSRSRDL -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -rdlFile "C:\Report.rdl" -force

.EXAMPLE
	Install-SSRSRDL "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" "C:\Report.rdl" -force

.EXAMPLE
	Install-SSRSRDL "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" "C:\Report.rdl" -force -reportName "MyReport"

.EXAMPLE
	Install-SSRSRDL "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" "C:\Report.rdl" -force -reportFolder "Reports" -reportName "MyReport"

#>
function Install-SSRSRDL
(
	[Parameter(Position=0,Mandatory=$true)]
	[Alias("url")]
	[string]$webServiceUrl,

	[ValidateScript({Test-Path $_})]
	[Parameter(Position=1,Mandatory=$true)]
	[Alias("rdl")]
	[string]$rdlFile,

	[Parameter(Position=2)]
	[Alias("folder")]
	[string]$reportFolder="",

	[Parameter(Position=3)]
	[Alias("name")]
	[string]$reportName="",

	[switch]$force
)
{
	$ErrorActionPreference="Stop"

	#Create Proxy
	Write-Host "[Install-SSRSRDL()] Creating Proxy, connecting to : $webServiceUrl"
	$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential
	$reportPath = "/"

	if($force)
	{
		#Check if folder is existing, create if not found
		try
		{
			$ssrsProxy.CreateFolder($reportFolder, $reportPath, $null)
			Write-Host "[Install-SSRSRDL()] Created new folder: $reportFolder"
		}
		catch [System.Web.Services.Protocols.SoapException]
		{
			if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
			{
				Write-Host "[Install-SSRSRDL()] Folder: $reportFolder already exists."
			}
			else
			{
				$msg = "[Install-SSRSRDL()] Error creating folder: $reportFolder. Msg: '{0}'" -f $_.Exception.Detail.InnerText
				Write-Error $msg
			}
		}

	}

	#Set reportname if blank, default will be the filename without extension
	if($reportName -eq "") { $reportName = [System.IO.Path]::GetFileNameWithoutExtension($rdlFile);}
	Write-Host "[Install-SSRSRDL()] Report name set to: $reportName"

	try
	{
		#Get Report content in bytes
		Write-Host "[Install-SSRSRDL()] Getting file content (byte) of : $rdlFile"
		$byteArray = gc $rdlFile -encoding byte
		$msg = "[Install-SSRSRDL()] Total length: {0}" -f $byteArray.Length
		Write-Host $msg

		$reportFolder = $reportPath + $reportFolder
		Write-Host "[Install-SSRSRDL()] Uploading to: $reportFolder"

		#Call Proxy to upload report
		$warnings = $ssrsProxy.CreateReport($reportName,$reportFolder,$force,$byteArray,$null)
		if($warnings.Length -eq $null) { Write-Host "[Install-SSRSRDL()] Upload Success." }
		else { $warnings | % { Write-Warning "[Install-SSRSRDL()] Warning: $_" }}
	}
	catch [System.IO.IOException]
	{
		$msg = "[Install-SSRSRDL()] Error while reading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Message
		Write-Error msg
	}
	catch [System.Web.Services.Protocols.SoapException]
	{
		$msg = "[Install-SSRSRDL()] Error while uploading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Detail.InnerText
		Write-Error $msg
	}

}

Uninstall SSRS RDL Script:


<#
.SYNOPSIS
	Uninstalls an RDL file from SQL Reporting Server using Web Service

.DESCRIPTION
	Uninstalls an RDL file from SQL Reporting Server using Web Service

.NOTES
	File Name: Uninstall-SSRSRDL.ps1
	Author: Randy Aldrich Paulo
	Prerequisite: SSRS 2008, Powershell 2.0

.EXAMPLE
	Uninstall-SSRSRDL -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -path "MyReport"

.EXAMPLE
	Uninstall-SSRSRDL -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -path "Reports/Report1"

#>
function Uninstall-SSRSRDL
(
	[Parameter(Position=0,Mandatory=$true)]
	[Alias("url")]
	[string]$webServiceUrl,

	[Parameter(Position=1,Mandatory=$true)]
	[Alias("path")]
	[string]$reportPath
)

{
	#Create Proxy
	Write-Host "[Uninstall-SSRSRDL()] Creating Proxy, connecting to : $webServiceUrl"
	$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential

	#Set Report Folder
	if(!$reportPath.StartsWith("/")) { $reportPath = "/" + $reportPath }

	try
	{

		Write-Host "[Uninstall-SSRSRDL()] Deleting: $reportPath"
		#Call Proxy to upload report
		$ssrsProxy.DeleteItem($reportPath)
		Write-Host "[Uninstall-SSRSRDL()] Delete Success."
	}
	catch [System.Web.Services.Protocols.SoapException]
	{
		$msg = "[Uninstall-SSRSRDL()] Error while deleting report : '{0}', Message: '{1}'" -f $reportPath, $_.Exception.Detail.InnerText
		Write-Error $msg
	}

}

Install-SSRSRDL Powershell Script
Uninstall-SSRSRDL Powershell Script

For permission check this blog: https://randypaulo.wordpress.com/2012/02/22/powershell-set-user-permission-in-ssrs-item-sql-reporting-server-using-powershell/

Advertisements
  1. kiquenet
    June 26, 2012 at 10:48 am

    When the function script call to

    $warnings = $ssrsProxy.CreateReport($reportName,$reportFolder,$force,$byteArray,$null)

    I get the error A positional parameter cannot be found that accepts argument -PositionalParameterNotFound Exception:

    $reportName,$reportFolder,$force,$byteArray has a value

    any suggestion about it ?

    • kiquenet
      June 26, 2012 at 12:39 pm

      If rdl file contains error, I get “A positional parameter cannot be found that accepts argument ” message when I call $ssrsProxy.CreateReport. Any suggestions for better error handling ?? thx

  2. June 27, 2012 at 4:02 am

    Add additional catch statement but this time catch the System.Exception error.

  3. kiquenet
    June 27, 2012 at 11:39 am

    Please, delete my comments, all is right

  4. Sarah
    July 2, 2012 at 3:53 pm

    I managed to get this script going. It uninstalls great. When I install, it seems to have stripped out my shared data source giving me an error when the report is run. “The report server cannot process the report. The data source connection information has been deleted. (rsInvalidDataSourceReference)” How can I replace the shared data source for this item. The other rdl files still work(the one i didnt uninstall and re-install yet).

  5. kiquenet
    July 20, 2012 at 8:38 am

    My issue about it:

    I install RDL files without Shared DataSource. All is right.

    Install-SSRSRDL -webServiceUrl $WebServiceSSRSRDL -rdlFile $report1 -force -reportFolder $FolderReports -reportName $reportName

    but I use RDL files WITH Shared DataSource, I get this error:

    I use this:

    Install-SSRSRDL -webServiceUrl $WebServiceSSRSRDL -rdlFile $report1 -force -reportFolder $FolderReports -reportName $reportName

    the log:

    [Install-SSRSRDL()] Creating Proxy, connecting to : http://RPS01/ReportServer_REPORTING/ReportService2005.asmx?WSDL

    [Install-SSRSRDL()] Folder: Reale already exists.

    [Install-SSRSRDL()] Report name set to: Report1ShareDS

    [Install-SSRSRDL()] Getting file content (byte) of : C:\Temp\Test.PowerShell\Report1ShareDS.rdl

    [Install-SSRSRDL()] Total length: 18058

    [Install-SSRSRDL()] Uploading to: /ReportFolder

    Error !!!

    ADVERTENCIA: Failed to access “” : A positional parameter cannot be found that
    accepts argument ‘

    ‘. in
    “C:\Temp\Deploy.Test.PowerShell\deploy.ReportingServices.ps1″
    Error
    Message: [Install-SSRSRDL()] Error desconocido rdl file : ‘C:\Temp\Test.PowerShell\Report1ShareDS.rdl’, Message: ”
    . StackTrace:
    FailedItem:
    Detail:

    Any suggestions.

  6. kiquenet
    August 9, 2012 at 7:30 am

    A questions about get info Report Server using powershell.

    I use SSRS 2008 R2.

    How can I get:

    Report Server DatabaseName
    Report Server Mode
    Report Service Status
    Sql Server Instance (Instance ID, Edition, Product Version)

    Thanks.

  7. December 14, 2012 at 2:24 pm

    In line 94 if you use:
    byteArray = [System.IO.File]::ReadAllBytes($rdlFile)

    It will serialize your report much quicker than using the GetContent command as it is quite memory consuming.

  8. kiquenet
    December 20, 2012 at 10:30 pm

    Yeah, GetContent is not good, bad performance

  9. January 16, 2013 at 2:47 pm

    It would be interesting check if report RDL exists or not using powershell.

    Similar like this:

    $rdl =TryGetList($rdlName)
    if($rdl -ne $null)
    {
    write-host -f green $rdlName “exists in the server”
    }
    else
    {
    write-host -f yellow $rdlName “does not exist in the server”
    }

  10. May 29, 2013 at 5:27 pm

    Found a few issues using this with 2012. First of course I had to change the ReportService2005 to 2010. Next, the line:

    $warnings = $ssrsProxy.CreateReport($reportName,$reportFolder,$force,$byteArray,$null)

    Fails, telling me CreateReport isn’t a valid method. I replaced it with these two lines:

    $warnings = @();
    $ssrsProxy.CreateCatalogItem(“Report”, $reportName, $reportFolder, $force, $byteArray, $null, [ref]$warnings)

    (Second line wraps, make sure it’s all one line when you copy and paste).

    Just thought I’d pass that along in case anyone else wanted to use the routine in SQL Server 2012. Great routine, thanks!

  11. June 10, 2013 at 4:23 pm

    Hi there.. I’m trying to deploy sccm reports with this script. Whenever i run this script the report folder is created in root. i want the folder to be created inside another Folder. Which variable should i edit for that ? Point me to the right direction.

  12. BrianDVS
    June 12, 2013 at 2:25 pm

    So I am trying to run this on a SQL2012 install and it just fails quietly, no logs, no reports imported, nothing.

    set-executionpolicy -scope Process -ExecutionPolicy Unrestricted -force

    ./Install-SSRSRDL “http://dvstestserver/ReportServer/ReportService2010.asmx?WSDL” “C:\drivers\Admin_Report.rdl” -force -reportFolder “Office Reports” -reportName “Admin_Report”

    I tried with ArcaneCode’s fix for 2012 but nothing…

    I also tried on a 2008 SQL same non-results.
    Anyone else having this issue?

  13. rpyzh
    May 8, 2014 at 10:12 pm

    This (ReportService2010) will also work in SSRS 2008 R2, by the way.

    Following ArcaneCode’s modifications, to print warnings, one should use:

    Write-Warning “[Install-SSRSRDL()] Warning: $($_.message)”

    instead of:

    Write-Warning “[Install-SSRSRDL()] Warning: $_”

  14. Lucas
    July 17, 2014 at 8:26 pm

    I’m not clear where I put the URL of the ReportServer that I am trying to upload the reports to. I have the rdl files saved in my c:\temp folder, and I want those uploaded and as well as the folders they are in to the destination reporting server. Where do I put that information in? Can someone please help?

  15. sss
    February 18, 2016 at 12:46 am

    can we possible add data source selection? this will make complete automation…

  16. January 11, 2017 at 10:04 am

    Got the following error can you help me

    [Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy72er_reportservice2010_asmx_wsdl.ReportingService2010] does
    not contain a method named ‘CreateReport’.

  1. February 24, 2016 at 2:00 pm

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: