Automated deployment of SSRS reports (PowerShell)

It all began with a client who wanted to distribute reports with minimal effort and make them usable by non-technical users. Minimal requirements were following:

• Should support SQL 2008 or higher
• No deployment or installation of the solution
• Minimal user explanation

So the hunt to a solution was on! I’ve found some scripts that did the trick by using PowerShell in combination with RSS. But after some trial (and lots of errors) these didn’t seem to include all the functionalities I wanted to provide to the client.

Search and find

After some researching the web I encountered this blogpost on TechNet by Srinivase Rao Gude. I’d found my sweet, sweet nectar. Still, this solution needed some additional work, to make it more generic, more honey flavored.

First up, I wanted the Shell to demand information from the user. This way, it wasn’t a fix for only one environment or client. The command to prompt users information from PowerShell:

Read-Host –Prompt

All values that are hardcoded in the script are changed into variables that are loaded via the prompted information.

In the process of testing on several machines, the PowerShell wasn’t usable before changing the Execution Policy. Therefore I’ve added some additional code in the script that either works or doesn’t via the script itself. I haven’t found the actual reason why sometimes it just accepts it and other times it doesn’t. However, when it doesn’t work right away, Copy-Paste the following piece of code and execute this before getting crazy with the script.

#Setting PowerShell Execution Policy
<# Syntax 
-ExecutionPolicy Policy 
A new execution policy for the shell. 
Bypass 
Nothing is blocked and there are no warnings or prompts. 
-Force 
Suppress all prompts. 
By default, Set-ExecutionPolicy displays a warning whenever the execution policy is changed. 
-Scope ExecutionPolicyScope 
The scope of the execution policy. 
Process <Affect only the current PowerShell process. 
#>
Set-ExecutionPolicy -scope Process -executionpolicy Bypass -force

I’ve chosen for myself to put the reports into the same directory under a folder called Reports.

As a final step I’ve added a shell command that opens Internet Explorer which opens the reporting URL to let the user see and taste the honey! It’s not the fasted code but it does the trick. You can find the code I’ve modified from Gude as source below.

Top tip: put the code into a flatfile (.txt) and rename it .ps1

With a right click and choosing the option to run with PowerShell, you can have a easy to use solution. Have fun with your automated SSRS deployment!!

:
try{
Write-Output "This script only runs in Powershell 2.0 or above"
#Requires -Version 2.0
}
catch {
$valError = $_.Exception.Message;
echo $_.Exception.Message;
pause
}

#Original Source: http://social.technet.microsoft.com/wiki/contents/articles/34521.powershell-script-for-ssrs-project-deployment.aspx

#Setting PowerShell Execution Policy
<# Syntax -ExecutionPolicy Policy A new execution policy for the shell. Bypass Nothing is blocked and there are no warnings or prompts. -Force Suppress all prompts. By default, Set-ExecutionPolicy displays a warning whenever the execution policy is changed. -Scope ExecutionPolicyScope The scope of the execution policy. Process Affect only the current PowerShell process. #>
Set-ExecutionPolicy -scope Process -executionpolicy Bypass -force

#Checking if User has Admin Rights
if (!([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator"))
{
Start-Process powershell.exe "-NoProfile -ExecutionPolicy Bypass -File `"$PSCommandPath`"" -Verb RunAs;
exit
}

#Set variables with configure values

$CurrentWorkingFolder = split-path -parent $MyInvocation.MyCommand.Definition

$Environment = "DEV" #specifiying Dev/Test/Prod Environment
$reportPath ="/" # Rool Level

#$SourceDirectory = "E:\Test" # Local drive where actual RDL/RDS/RSD files contains
$SourceDirectory = "$CurrentWorkingFolder\Reports"

# Set server IP address based on Environment provided from Config file
IF( $Environment -eq "DEV")
{
$webServiceUrl = Read-Host -Prompt 'Reporting Host URL (Http://hostname or Http://xxx.xxx.xx.xx)' # you can also give like "Http://xxx.xxx.xx.xx"
$RPServerName = Read-Host -Prompt 'Reporting server name (ReportServer)'#"ReportServer"
$reportFolder = Read-Host -Prompt 'Folder to be deployed TO'
$DataSourcePath = "Data Sources" #Folder where we need to create Datasource
$DataSet_Folder = "Datasets" # Folder where we need to create DataSet
}
<#ELSEIF ( $Environment -eq "TEST") { $webServiceUrl = "http://localhost" $RPServerName = "ReportServer" $reportFolder = "TEST_DemoReports" $DataSourcePath = "/TEST_DemoReports" #Folder where we need to create Datasource $DataSet_Folder = "/TEST_DemoReports" # Folder where we need to create DataSet } ELSEIF ($Environment -eq "PROD") { $webServiceUrl = "http://localhost" $RPServerName = "ReportServer" $reportFolder = "PROD_DemoReports" $DataSourcePath = "/PROD_DemoReports" #Folder where we need to create Datasource $DataSet_Folder = "/PROD_DemoReports" # Folder where we need to create DataSet }#>

#Overwrite properties
$IsOverwriteDataSource = switch (Read-Host -Prompt 'Overwrite DataSources? [Y],[N]')
{
"Y" {[Boolean] 1}
default {[Boolean] 0}
}
$IsOverwriteDataSet = switch (Read-Host -Prompt 'Overwrite DataSet? [Y],[N]')
{
"Y" {[Boolean] 1}
default {[Boolean] 0}
}
$IsOverwriteReport = switch (Read-Host -Prompt 'Overwrite Reports? [Y],[N]')
{
"N" {[Boolean] 0}
default {[Boolean] 1}
}

#Connecting to SSRS
Write-Host "Reportserver: $webServiceUrl" -ForegroundColor Magenta
Write-Host "Creating Proxy, connecting to : $webServiceUrl/$RPServerName/ReportService2010.asmx?WSDL" #Version SQL2012
Write-Host ""
$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl'/'$RPServerName'/ReportService2010.asmx?WSDL' -UseDefaultCredential

$reportFolder_Final = $reportPath + $reportFolder
$dataSourceFolder_Final = $reportPath + $DataSourcePath
$dataSetFolder_Final = $reportPath + $DataSet_Folder

##########################################
#Create Report Folder
Write-host ""
try
{
$ssrsProxy.CreateFolder($reportFolder, $reportPath, $null)
Write-Host "Created new folder: $reportFolder_Final"
}
catch [System.Web.Services.Protocols.SoapException]
{
if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
{
Write-Host "Folder: $reportFolder already exists."
}
else
{
$msg = "Error creating folder: $reportFolder. Msg: '{0}'" -f $_.Exception.Detail.InnerText
Write-Error $msg
}

}
##########################################
#Create DataSource Folder
Write-host ""
try
{
$ssrsProxy.CreateFolder($DataSourcePath, $reportPath, $null)
Write-Host "Created new folder: $dataSourceFolder_Final"
}
catch [System.Web.Services.Protocols.SoapException]
{
if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
{
Write-Host "Folder: $DataSourcePath already exists."
}
else
{
$msg = "Error creating folder: $DataSourcePath. Msg: '{0}'" -f $_.Exception.Detail.InnerText
Write-Error $msg
}

}
##########################################
#Create DataSet Folder
Write-host ""
try
{
$ssrsProxy.CreateFolder($DataSet_Folder, $reportPath, $null)
Write-Host "Created new folder: $dataSetFolder_Final"
}
catch [System.Web.Services.Protocols.SoapException]
{
if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
{
Write-Host "Folder: $DataSet_Folder already exists."
}
else
{
$msg = "Error creating folder: $DataSet_Folder. Msg: '{0}'" -f $_.Exception.Detail.InnerText
Write-Error $msg
}

}

##########################################
#Create datasource

foreach($rdsfile in Get-ChildItem $SourceDirectory -Filter *.rds)
{
Write-host $rdsfile

#create data source
try
{

$rdsf = [System.IO.Path]::GetFileNameWithoutExtension($rdsfile);

$RdsPath = $SourceDirectory+"\"+$rdsf+".rds"

Write-host "Reading data from $RdsPath"

[xml]$Rds = Get-Content -Path $RdsPath
$ConnProps = $Rds.RptDataSource.ConnectionProperties

$type = $ssrsProxy.GetType().Namespace
$datatype = ($type + '.DataSourceDefinition')
$datatype_Prop = ($type + '.Property')

$DescProp = New-Object($datatype_Prop)
$DescProp.Name = 'Description'
$DescProp.Value = ''
$HiddenProp = New-Object($datatype_Prop)
$HiddenProp.Name = 'Hidden'
$HiddenProp.Value = 'false'
$Properties = @($DescProp, $HiddenProp)

$Definition = New-Object ($datatype)
$Definition.ConnectString = $ConnProps.ConnectString
$Definition.Extension = $ConnProps.Extension
if ([Convert]::ToBoolean($ConnProps.IntegratedSecurity)) {
$Definition.CredentialRetrieval = 'Integrated'
}

$DataSource = New-Object -TypeName PSObject -Property @{
Name = $Rds.RptDataSource.Name
Path = $dataSourceFolder_Final + '/' + $Rds.RptDataSource.Name
}

<# if ($IsOverwriteDataSource -eq 1) { [boolean]$IsOverwriteDataSource = 1 } else { [boolean]$IsOverwriteDataSource = 0 } #>

$warnings = $ssrsProxy.CreateDataSource($rdsf, $dataSourceFolder_Final ,$IsOverwriteDataSource, $Definition, $Properties)

# Write-Host $warnings

}
catch [System.IO.IOException]
{
$msg = "Error while reading rds file : '{0}', Message: '{1}'" -f $rdsfile, $_.Exception.Message
Write-Error msgcler
}
catch [System.Web.Services.Protocols.SoapException]
{
if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
{
Write-Host "DataSource: $rdsf already exists."
}
else
{

$msg = "Error uploading report: $rdsf. Msg: '{0}'" -f $_.Exception.Detail.InnerText
Write-Error $msg
}

}
}

##########################################
# Create Dataset
Write-host "dataset changes start"
foreach($rsdfile in Get-ChildItem $SourceDirectory -Filter *.rsd)
{
Write-host ""

$rsdf = [System.IO.Path]::GetFileNameWithoutExtension($rsdfile)
$RsdPath = $SourceDirectory+'\'+$rsdf+'.rsd'

Write-Verbose "New-SSRSDataSet -RsdPath $RsdPath -Folder $DataSet_Folder"

$RawDefinition = Get-Content -Encoding Byte -Path $RsdPath
$warnings = $null

$Results = $ssrsProxy.CreateCatalogItem("DataSet", $rsdf, $dataSetFolder_Final, $IsOverwriteDataSet, $RawDefinition, $null, [ref]$warnings)

write-host "dataset created successfully"

}

#############################
#For each RDL file in Folder

foreach($rdlfile in Get-ChildItem $SourceDirectory -Filter *.rdl)
{
Write-host ""

#ReportName
$reportName = [System.IO.Path]::GetFileNameWithoutExtension($rdlFile);
write-host $reportName -ForegroundColor Green
#Upload File
try
{
#Get Report content in bytes
Write-Host "Getting file content of : $rdlFile"
$byteArray = gc $rdlFile.FullName -encoding byte
$msg = "Total length: {0}" -f $byteArray.Length
Write-Host $msg

Write-Host "Uploading to: $reportFolder_Final"

$type = $ssrsProxy.GetType().Namespace
$datatype = ($type + '.Property')

$DescProp = New-Object($datatype)
$DescProp.Name = 'Description'
$DescProp.Value = ''
$HiddenProp = New-Object($datatype)
$HiddenProp.Name = 'Hidden'
$HiddenProp.Value = 'false'
$Properties = @($DescProp, $HiddenProp)

#Call Proxy to upload report

$warnings = $null

$Results = $ssrsProxy.CreateCatalogItem("Report", $reportName,$reportFolder_Final, $IsOverwriteReport,$byteArray,$Properties,[ref]$warnings)

if($warnings.length -le 1)
{ Write-Host "Upload Success." -ForegroundColor Green
}
else
{ write-host $warnings
}

}
catch [System.IO.IOException]
{
$msg = "Error while reading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Message
Write-Error msg
}
catch [System.Web.Services.Protocols.SoapException]
{

$msg = "Error uploading report: $reportName. Msg: '{0}'" -f $_.Exception.Detail.InnerText
Write-Error $msg

}

##########################################
##Change Datasource on report
$reportFullName = $reportFolder_Final+"/"+$reportName
Write "datasource record $reportFullName"

$rep = $ssrsProxy.GetItemDataSources($reportFullName)
$rep | ForEach-Object {
$proxyNamespace = $_.GetType().Namespace

$constDatasource = New-Object ("$proxyNamespace.DataSource")

$constDatasource.Item = New-Object ("$proxyNamespace.DataSourceReference")
$FinalDatasourcePath = $dataSourceFolder_Final+"/" + $($_.Name)
$constDatasource.Item.Reference = $FinalDatasourcePath

$_.item = $constDatasource.Item
$ssrsProxy.SetItemDataSources($reportFullName, $_)
Write-Host "Changing datasource `"$($_.Name)`" to $($_.Item.Reference)"
}

}

Write-host ""
Write-host " We have successfully Deployed SSRS Project" -ForegroundColor Magenta
Write-host ""

#Open IE
$RPServernameUI = $RPServerName.Replace('ReportServer','Reports')
Start-Process "iexplore.exe" $webServiceUrl'/'$RPServernameUI"/Pages/Folder.aspx"