SSRS Deployment Automation Snippets

DeployReports.ps1

##################################################################
### 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
}

DeploySQL.ps1

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
}

Report_Datasource.ps1

$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)
}

Report_Folder.ps1

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

Report_Upload.ps1

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)
}

Reports.nuspec

<?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>