Simplifying SSRS .rdl Files Deployment with GitHub Actions

Prashanth Kumar
5 min readApr 19, 2024

--

One of the biggest challenge when it comes to deploying some of the old reporting services (.rdl) files on SSRS as its a old way and there is no unique way to upload and comes with own issues.

In order to simplify our team has created a simplified PowerShell script to pull .rdl files from Github Repository and then deploying it to SSRS server URL.

Understanding the Workflow:

This article focuses on creating an automated workflow for deploying SSRS .rdl files, specifically tailored for the internal project(s). The workflow is initiated manually through GitHub’s Workflow_dispatch event, allowing users to specify the report name as an input parameter, thus ensuring flexibility and control over the deployment process.

Implementation Details:

  1. Created new .rdl file and uploaded onto Github repository.

2. Now lets look into workflow to pull and deploy onto Reporting services URL.

In order to proceed with the deployment the first step is to make sure we need to install “ReportingServicesTools” module. For that I am using below command

 - name: Install ReportingServicesTools module
run: |
& "${env:SystemRoot}\system32\WindowsPowerShell\v1.0\powershell.exe" -Command "Install-Module -Name ReportingServicesTools -Scope CurrentUser -Force"

Make sure you define right path for PowerShell or else you will receive error with “pwsh” not found.

3. Next step is to make sure we pick .rdl file from right Github repo path.

for that I am using below workflow.

 - name: Deploy SSRS Reports
run: |

$sourceFolder = "${{ github.workspace }}\${{ env.WORKING_DIRECTORY }}SSRS\Reports\TrackerReports"
$targetReportServerUri = "http://reportingservices.abc.com/Reports"
$targetFolder = "/TrackerReports" #considering if you have multiple folders hosted.
$reportName = "${{ github.workspace }}\${{ env.WORKING_DIRECTORY }}SSRS\Reports\TrackerReports\${{ github.event.inputs.report_name }}" #either it can pick from workflow or else you may need to add .rdl file name

Write-Output "Deploying SSRS Reports"
Write-Output "Source Folder: $sourceFolder"
Write-Output "Target Server: $targetReportServerUri"
Write-Output "Target Folder: $targetFolder"

Write-Output "Installing SQL Server Reporting Services PowerShell utilities..."
Install-Module -Name ReportingServicesTools -Force -Scope CurrentUser

Write-Output "Write-RsCatalogItem: $targetReportServerUri $reportName $targetFolder"
Write-RsCatalogItem -ReportServerUri $targetReportServerUri -Path $reportName -Destination $targetFolder -OverWrite

4. Now lets look into actual Github workflow in action, before we trigger Github workflow if i go to http://reportingservices.abc.com/Reports/browse/

Currently I have some old set of files (total 11).

5. Lets trigger the pipeline, I just passed the reporting .rdl file name which needs to be deployed.

Important: Make sure you add your Github self hosted machine agent in SSRS security permissions list.

6. Now lets launch the URL again to make sure it is deployed successfully, you can see a new deployed .rdl file.

Full workflow file


name: Reporting .rdl files deployment

on:
workflow_dispatch:
inputs:
report_name:
type: string
required: true
description: report full name. e.g Test-reporting-file.rdl

jobs:
get-environment:
runs-on: [self-hosted, Windows, MachineName]
outputs:
EnvName: ${{ steps.extract_env_name.outputs.EnvName }}
steps:
- name: Extract branch name
id: extract_env_name
run: |

$EnvName = $env:GITHUB_REF -replace "refs/heads/", ""


if ($EnvName -eq "main") {

Add-Content -Path $env:GITHUB_OUTPUT -Value "EnvName=prod"
} else {

Add-Content -Path $env:GITHUB_OUTPUT -Value "EnvName=test"
}


Write-Host "Environment name has been set based on GITHUB_REF."

deploy-report:
needs: get-environment
name: build and deploy reports ${{ needs.get-environment.outputs.EnvName }}

runs-on: [self-hosted, Windows, MachineName]
steps:
- name: Checkout
uses: actions/checkout@v3



- name: Install ReportingServicesTools module
run: |
& "${env:SystemRoot}\system32\WindowsPowerShell\v1.0\powershell.exe" -Command "Install-Module -Name ReportingServicesTools -Scope CurrentUser -Force"

- name: Deploy SSRS Reports
run: |

$sourceFolder = "${{ github.workspace }}\${{ env.WORKING_DIRECTORY }}SSRS\Reports\TrackerReports"
$targetReportServerUri = "http://reportingServicesURL.abc.com/Reports"
$targetFolder = "/FolderPath"
$reportName = "${{ github.workspace }}\${{ env.WORKING_DIRECTORY }}SSRS\Reports\TrackerReports\${{ github.event.inputs.report_name }}"

Write-Output "Deploying SSRS Reports"
Write-Output "Source Folder: $sourceFolder"
Write-Output "Target Server: $targetReportServerUri"
Write-Output "Target Folder: $targetFolder"

Write-Output "Installing SQL Server Reporting Services PowerShell utilities..."
Install-Module -Name ReportingServicesTools -Force -Scope CurrentUser

Write-Output "Write-RsCatalogItem: $targetReportServerUri $reportName $targetFolder"
Write-RsCatalogItem -ReportServerUri $targetReportServerUri -Path $reportName -Destination $targetFolder -OverWrite



####added/modified data source ############

- name: Repoint data source
run: |

Write-Output "Repointing data source on report..."
$WebServiceUri="${{vars.SSRS_SERVER}}/ReportServer/ReportService2010.asmx?wsdl"
$proxy=New-WebServiceProxy -uri $WebServiceUri -UseDefaultCredential

# Create data source object
$typeds=($proxy.gettype().namespace) + '.DataSource'
$ds=new-object -TypeName $typeds

$typedsref=($proxy.gettype().namespace) + '.DataSourceReference'
$reference=new-object -TypeName $typedsref

# Set data source object with reference and name
$reference.Reference="/Tracker" # path/name of the data source
$ds.Item=$reference
$ds.Name="Tracker" # name of the data source on the report

# Set data source on the report
$ReportPath = "/Tracker/${{ github.event.inputs.report_name }}"
$ReportPath = $ReportPath -replace '\.rdl$', ''
$proxy.SetItemDataSources($ReportPath, $ds)

Conclusion:

By harnessing the power of GitHub Actions, teams can automate the deployment of SSRS .rdl files effortlessly, eliminating manual intervention and reducing the risk of errors. This streamlined approach not only enhances efficiency but also fosters consistency and reliability across the deployment pipeline. As organizations continue to embrace automation and DevOps practices, GitHub Actions emerges as a valuable tool in simplifying and accelerating software development workflows. Later you can migrate the same using next stage step.

Make sure you point it to right Variables from your Github Environment Variables/Secrets.

--

--