Hello SSRS

For one of our clients we have a report server in each of the environments, and when a new report is released it should be deployed everywhere. Unfortunately the Live and DR environments for this customer are completely air gapped, meaning we can’t keep them in sync without travelling to the data centre where they exist.

We also don’t want all the reports in the Dev server synced to the UAT server as there will be unfinished and broken reports in there that aren’t ready for user tests.

Enter Powershell

Param(
    [string]$ReportName,
    [string]$NewReportName,
    [string]$FilePath,
    [string]$ReportServer,
    [string]$ArchiveFolderName
)
Switch ($ReportServer) {
    'Dev'   {$RsServer = 'https://dev-server/reportserver'}
    'UAT'   {$RsServer = 'https://uat-server/reportserver'}
    'DR'    {$RsServer = 'https://dr-server/reportserver'}
    'Live'  {$RsServer = 'https://live-server/reportserver'}
    default {$RsServer = $null}
}

If (!$ArchiveFolderName) {
    $ArchiveFolderName = 'Archive'
}

$ReportPath = Get-RsCatalogItems -ReportServerUri $RsServer -Path /ReportTRootFolder -Recurse | Where-Object Name -eq $ReportName | Select-Object -ExpandProperty Path

$UpperLevelPath = $ReportPath.Replace('/'+$ReportName,'')
$ArchivePath = $UpperLevelPath + '/' + $ArchiveFolderName
$ArchivePathCheck = Get-RsCatalogItems -ReportServerUri $RsServer -Path $UpperLevelPath -Recurse | Where-Object Name -eq $ArchiveFolderName | Select-Object -ExpandProperty Path

If (!$ArchivePathCheck) {
    New-RsFolder -ReportServerUri $RsServer -RsFolder $UpperLevelPath -FolderName $ArchiveFolderName
}
Out-RsCatalogItem -ReportServerUri $RsServer -RsItem $ReportPath -Destination $FilePath
Write-RsCatalogItem -ReportServerUri $RsServer -Path $FilePath'\'$ReportName'.rdl' -RsFolder $ArchivePath

Remove-RsCatalogItem -ReportServerUri $RsServer -RsItem $ReportPath

Write-RsCatalogItem -ReportServerUri $RsServer -Path $FilePath'\'$NewReportName'.rdl' -RsFolder $UpperLevelPath

This is a cool way to upload a report to the given report server, removing the manual effort required. It may only be a little time saver but it’s a cool way to get yourself introduced to the SSRS Powershell module.

That’s it, just a short one, thanks for reading.

Back to posts