kohera-logo-regular.svg

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"

$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"

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...