Skip to content

Instantly share code, notes, and snippets.

@sbedford
Last active June 16, 2020 11:59
Show Gist options
  • Save sbedford/0693ab338a86aed34d98 to your computer and use it in GitHub Desktop.
Save sbedford/0693ab338a86aed34d98 to your computer and use it in GitHub Desktop.
SSRS Deployment Automation Snippets
##################################################################
### The following parameters are provided by Octopus Deploy. ###
### Uncomment these with default values for testing. ###
#################################################################
#$SSRSReportServerUrl = "http://ssrs/reportserver/reportservice2005.asmx"
#$SSRSDynamicDataSourceCredentialsUsername = ""
#$SSRSDynamicDataSourceCredentialsPassword = ""
#$SSRSReportFolder = "Project Folder"
#$SSRSSharedDataSourcePath = "/Data Sources/dsShared"
#$environment = "Environment Folder";
# This value is also supplied by Octopus Deploy. But we are aliasing the variable
# here to make testing the script easier.
$environment = $OctopusParameters["Octopus.Environment.Name"]
$reportPath = "/"
Write-Host -fore Green "Deploying reports to " $ssrsreportserverurl
# If the Reporting Services Root (destination) directory does not exist - create it.
$ssrsProxy = New-WebServiceProxy -Uri $ssrsreportserverurl -UseDefaultCredential
try
{
$ssrsProxy.CreateFolder($SSRSReportFolder, $reportPath, $null)
Write-Host "Created new folder: $SSRSReportFolder"
}
catch [System.Web.Services.Protocols.SoapException]
{
if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
{
Write-Host -fore Yellow "Folder: $SSRSReportFolder already exists."
}
else
{
$msg = "Error creating folder: $SSRSReportFolder. Msg: '{0}'" -f $_.Exception.Detail.InnerText
Write-Error $msg
}
}
# If the Reporting Services Subfolder (destination) directory does not exist - create it.
$reportPath = [string]::Concat($reportPath, $SSRSReportFolder)
#Check if folder is existing, create if not found
try
{
$ssrsProxy.CreateFolder($environment, $reportPath, $null)
Write-Host -fore Green "Created new folder: $environment"
}
catch [System.Web.Services.Protocols.SoapException]
{
if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
{
Write-Host -fore Yellow "Folder: $environment already exists."
}
else
{
$msg = "Error creating folder: $environment. Msg: '{0}'" -f $_.Exception.Detail.InnerText
Write-Error $msg
}
}
function Install-SSRSRDL
(
[ValidateScript({Test-Path $_})]
[Parameter(Position=0,Mandatory=$true)]
[Alias("rdl")]
[string]$rdlFile,
[Parameter(Position=1)]
[Alias("folder")]
[string]$reportFolder="",
[Parameter(Position=2)]
[Alias("name")]
[string]$reportName="",
[Parameter(Position=3)]
[string]$reportCredentialsUsername="",
[Parameter(Position=4)]
[string]$reportCredentialsPassword="",
[switch]$force
)
{
#Set reportname if blank, default will be the filename without extension
if($reportName -ne "") {
$reportName = [System.IO.Path]::GetFileNameWithoutExtension($rdlFile);
}
try
{
#Get Report content in bytes
$byteArray = gc $rdlFile -encoding byte
Write-Host "Uploading $reportName to $reportFolder"
$warnings = $ssrsProxy.CreateReport($reportName,$reportFolder,$force,$byteArray,$null)
if($warnings -eq $null) {
Write-Host "Report uploaded"
}
else {
$warnings | % { Write-Warning $_ }
}
$found = 0
$dsPath = [string]::Concat($reportFolder, "/", $reportName)
$ds = $ssrsProxy.GetItemDataSources($dsPath)
$ds | ForEach-Object {
if($_.Name -eq "dsDynamic") {
if ($reportCredentialsUsername -ne "") {
Write-Host "Datasource dsDynamic found. Setting credentials"
$_.Item.CredentialRetrieval = "Store"
$_.Item.UserName = $reportCredentialsUsername
$_.Item.Password = $reportCredentialsPassword
$found = 1
}
else {
Write-Error ("Report $reportName had dynamic data source dsDynamic but no reportCredentialsUsername variable provided")
}
} elseif ($_.Name -eq "dsTAS") {
Write-Host "Shared datasource found. setting path"
if ($SSRSSharedDataSourcePath -ne $null){
$proxyNamespace = $ssrsProxy.GetType().Namespace
$_.Item = New-Object("$proxyNamespace.DataSourceReference")
$_.Item.Reference = $SSRSSharedDataSourcePath
$found = 1
}
else{
Write-Error ("Report $reportName had shared data source reference dsTAS but no SSRSdsTASSharedDataSourcePath variable provided")
}
}
}
if ($found -eq 1){
$ssrsProxy.SetItemDataSources($dsPath, $ds)
}
}
catch [System.IO.IOException]
{
Write-Error ("Error while reading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Message)
}
catch [System.Web.Services.Protocols.SoapException]
{
Write-Error ("Error while uploading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Detail.InnerText)
}
}
$reportPath = [string]::Concat($reportPath, "/", $environment)
Get-ChildItem './Reports' -Filter *.rdl |
Foreach-Object{
Install-SSRSRDL $_.FullName -force -reportFolder $reportPath -reportName $_.Name -reportCredentialsUsername $ssrsDynamicdatasourceCredentialsUsername -reportCredentialsPassword $ssrsDynamicDatasourceCredentialsPassword
}
function Invoke-SqlPlusWithFile($connectionString, $file) {
Write-Output "$file";
# Wrap script to make sqlplus stop on errors (because Oracle doesn't do this
# by default). Also exit the prompt after the script has run.
$lines = Get-Content $file;
$lines = ,'whenever sqlerror exit sql.sqlcode rollback;' + $lines;
$lines += 'exit';
Invoke-SqlPlusCommand $connectionString $lines
}
function Invoke-SqlPlusCommand($connectionString, $command) {
$command | sqlplus $connectionString | Tee-Object -Variable output | out-default;
if (!$? -or # Stop on non-zero exit codes.
# Stop on script errors. Have to detect them from output
# unfortunately, as I couldn't find a way to make SQL*Plus halt on
# warnings.
$output -match "compilation errors" -or
$output -match "unknown command" -or
$output -match "Input is too long" -or
$output -match "unable to open file") {
throw "Script failed: $fileNameOnly";
}
}
$environment = $OctopusParameters["Octopus.Environment.Name"] ;
$version = $OctopusParameters["Octopus.Release.Number"];
$versionCommand = "
INSERT INTO VersionInfo VALUES ('$environment','$version', sysdate);
COMMIT;
/";
foreach ($connection in $nEDRMappingConnectionStrings.Split(","))
{
Write-Host "Executing scripts for Connection $connection"
$files = Get-ChildItem "./SQL/Scripts/Dependencies" -Filter *.sql | Sort-Object
foreach ($file in $files) {
Invoke-SqlPlusWithFile $connection $file.FullName
}
$files = Get-ChildItem "./SQL/Scripts" -Filter *.sql | Sort-Object
foreach ($file in $files) {
Invoke-SqlPlusWithFile $connection $file.FullName
}
Invoke-SqlPlusCommand $connection $versionCommand
}
$dsPath = [string]::Concat($reportFolder, "/", $reportName)
$ds = $ssrsProxy.GetItemDataSources($dsPath)
$ds | ForEach-Object {
# Embedded Datasource
if($_.Name -eq "dsDynamic") {
if ($reportCredentialsUsername -ne "") {
Write-Host "Datasource dsDynamic found. Setting credentials"
$_.Item.CredentialRetrieval = "Store"
$_.Item.UserName = $reportCredentialsUsername
$_.Item.Password = $reportCredentialsPassword
$found = 1
}
# Shared Datasource
} elseif ($_.Name -eq "dsTAS") {
if ($SSRSSharedDataSourcePath -ne $null){
$proxyNamespace = $ssrsProxy.GetType().Namespace
$_.Item = New-Object("$proxyNamespace.DataSourceReference")
$_.Item.Reference = $SSRSSharedDataSourcePath
$found = 1
}
}
}
if ($found -eq 1){
$ssrsProxy.SetItemDataSources($dsPath, $ds)
}
$environment = $OctopusParameters["Octopus.Environment.Name"]
# create proxy object to http://ssrs/reportserver/reportservice2005.asmx
$ssrsProxy = New-WebServiceProxy -Uri $ssrsreportserverurl -UseDefaultCredential
# create top level (project) folder
$ssrsProxy.CreateFolder($SSRSReportFolder, $reportPath, $null)
# create the environment sub folder.
$reportPath = [string]::Concat($reportPath, $SSRSReportFolder)
$ssrsProxy.CreateFolder($environment, $reportPath, $null)
Get-ChildItem './Reports' -Filter *.rdl |
Foreach-Object{
$byteArray = gc $rdlFile -encoding byte
Write-Host "Uploading $reportName to $reportFolder"
$ssrsProxy.CreateReport($reportName,$reportFolder,$force,$byteArray,$null)
}
<?xml version="1.0"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
<metadata>
<id>Reports</id>
<title>Report PAckage</title>
<version>1.0.0</version>
<authors>Ajilon</authors>
<owners>Ajilon</owners>
<requireLicenseAcceptance>false</requireLicenseAcceptance>
<description>Contains all SQL Server Reporting Services reports and scripts required to create a fully functional environment</description>
</metadata>
<files>
<file src="*.rdl" target="Reports" />
<file src="..\sql\*.sql" target="Sql" />
<file src="..\sql\Scripts\*.sql" target="Sql\Scripts" />
<file src="..\sql\Scripts\Dependencies\*.sql" target="Sql\Scripts\Dependencies" />
<file src="*.ps1" target="" />
<file src="..\*.ps1" target="" />
</files>
</package>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment