Category / Section
Automating SSRS to Bold Reports Migration Using PowerShell Scripts
Published:
Updated:
Overview
Automatic migration from SSRS to Bold Reports allows you to transfer reports, shared data sources, and datasets with minimal manual effort using PowerShell scripts and Bold Reports REST APIs. This process involves exporting SSRS resources, migrating data sources and datasets to Bold Reports, and uploading reports into corresponding categories in Bold Reports, ensuring a streamlined and efficient migration.
This guide shows how to:
- Export all SSRS items (reports, data sources, datasets, resources) to disk.
- Migrate shared data sources (.rsds files) from SSRS to Bold Reports.
- Migrate shared datasets (.rsd files) from SSRS to Bold Reports.
- Migrate categories from SSRS to Bold Reports and upload .rdl reports to their matching categories.
Tip: For smooth migration, keep SSRS item names consistent. The scripts rely on name matching to bind datasets to data sources and to organize reports into categories.
Prerequisites
- Access to SSRS ReportServer with permission to read/export all items.
- Bold Reports Server URL and Site/Tenant Identifier (e.g., site1).
- A valid Bearer Token for Bold Reports REST API with permission to create items.
- PowerShell (Windows), and local write permissions to the export folder.
Step 1 — Export all SSRS resources
Download all resources from SSRS using the script below:
$sourceRsUri = '{{SSRS Server url}}/ReportServer/ReportService2010.asmx?wsdl'
$proxy = New-RsWebServiceProxy -ReportServerUri $sourceRsUri
Out-RsFolderContent -Proxy $proxy -RsFolder / -Destination 'C:\SSRS_Out' -Recurse
Step 2 — Migrate SSRS shared data sources to Bold Reports
Run the PowerShell script below to migrate SSRS shared data sources to Bold Reports server.
# --- Inputs ---
$BaseUrl = "https://{yourdomain}"
$Tenant = "SiteIdentifier"
$Endpoint = "$BaseUrl/reporting/api/site/$Tenant/v1.0/reports/data-sources" # note: data-sources (with hyphen)
$BearerToken = "Replace Your Token"
# Folder containing .rsds files
$FolderPath = "C:\SSRS_Out"
# --- Headers ---
$headers = @{
"Authorization" = "Bearer $BearerToken"
"Content-Type" = "application/json"
}
# --- Helper: map SSRS <Extension> to Bold Reports ServerType strings ---
function Get-ServerTypeFromExtension {
param([string]$ext)
if ([string]::IsNullOrWhiteSpace($ext)) {
return "SQL"
}
switch ($ext.ToUpper()) {
"SQL" { return "SQL" }
"SQLCE" { return "SQLCE" }
"OLEDB" { return "OLEDB" }
"ODBC" { return "ODBC" }
"ORACLE" { return "Oracle" }
"XML" { return "XML" }
"SSAS" { return "SSAS" }
"POSTGRESQL" { return "PostgreSQL" }
"MYSQL" { return "MySQL" }
"WEBAPI" { return "WebApi" }
default { return "SQL" } # sensible default
}
}
# --- Get .rsds files ---
$files = Get-ChildItem -Path $FolderPath -Filter *.rsds -File
if (-not $files -or $files.Count -eq 0) {
Write-Warning "No .rsds files found in '$FolderPath'."
return
}
foreach ($f in $files) {
# Datasource name = file name without extension
$dsName = [System.IO.Path]::GetFileNameWithoutExtension($f.Name)
try {
# Load XML safely
$xmlText = Get-Content -LiteralPath $f.FullName -Raw
[xml]$xml = $xmlText
# Extract ConnectString and Extension using XPath
$connectNode = $xml.SelectSingleNode("//DataSourceDefinition/ConnectString")
$extensionNode = $xml.SelectSingleNode("//DataSourceDefinition/Extension")
if ($connectNode -eq $null -or [string]::IsNullOrWhiteSpace($connectNode.InnerText)) {
Write-Warning "Skipping '$($f.Name)' — no <ConnectString> found."
continue
}
$connectString = $connectNode.InnerText.Trim()
$extension = $null
if ($extensionNode -ne $null -and -not [string]::IsNullOrWhiteSpace($extensionNode.InnerText)) {
$extension = $extensionNode.InnerText.Trim()
}
$serverType = Get-ServerTypeFromExtension $extension
# Build payload as an object to avoid quoting issues
$payload = [PSCustomObject]@{
Name = $dsName
Description = "Auto-created from RSDS file: $($f.Name)"
DataSourceDefinition = [PSCustomObject]@{
ConnectString = $connectString
CredentialRetrieval = "None"
ImpersonateUser = $false
WindowsCredentials = $false
ServerType = $serverType
}
}
$bodyJson = $payload | ConvertTo-Json -Depth 8 -Compress
Write-Host "`nPosting datasource: $dsName (ServerType=$serverType)"
$response = Invoke-RestMethod -Method POST -Uri $Endpoint -Headers $headers -Body $bodyJson -ErrorAction Stop
if ($response.Status -and $response.ApiStatus) {
Write-Host "✅ Created. PublishedItemId: $($response.PublishedItemId)"
} else {
Write-Warning "⚠️ API returned non-success status for '$dsName'."
$response | ConvertTo-Json -Depth 6
}
}
catch {
# Capture HTTP error details
$httpResp = $_.Exception.Response
if ($httpResp) {
try {
$statusCode = [int]$httpResp.StatusCode
} catch {
$statusCode = $null
}
# Handle 409 Conflict explicitly: already exists
if ($statusCode -eq 409) {
Write-Warning "⏭️ Skipped: Datasource '$dsName' already exists (409 Conflict)."
continue
}
# Print raw server response for other errors
$sr = New-Object System.IO.StreamReader($httpResp.GetResponseStream())
$raw = $sr.ReadToEnd()
Write-Error "❌ Failed to add '$dsName': HTTP $statusCode"
Write-Host "--- Raw Server Response ---"
Write-Host $raw
} else {
Write-Error "❌ Failed to add '$dsName': $($_.Exception.Message)"
}
}
}
Result: All SSRS shared data sources are migrated in Bold Reports.
Important: After all shared data sources are successfully uploaded, update the username and password manually by editing each shared data source in Bold Reports. This ensures proper authentication for your reports.
Step 3 — Migrate SSRS shared datasets in Bold Reports
Run the PowerShell script below to import SSRS shared datasets into Bold Reports and map them to the appropriate data sources.
# === Inputs ===
$BaseUrl = "https://{yourdomain}"
$Tenant = "Site Identifier"
$Endpoint = "$BaseUrl/reporting/api/site/$Tenant/v1.0/reports/datasets"
$BearerToken = "Token"
$FolderPath = "C:\SSRS_Out"
# === Headers ===
$headers = @{
"Authorization" = "Bearer $BearerToken"
"Content-Type" = "application/json"
}
# === Get .rsd files ===
$files = Get-ChildItem -Path $FolderPath -Filter *.rsd -File
if (-not $files -or $files.Count -eq 0) {
Write-Warning "No .rsd files found in '$FolderPath'."
return
}
foreach ($f in $files) {
$datasetName = [System.IO.Path]::GetFileNameWithoutExtension($f.Name)
try {
# Parse XML
$xmlText = Get-Content -LiteralPath $f.FullName -Raw
[xml]$xml = $xmlText
$ns = New-Object System.Xml.XmlNamespaceManager($xml.NameTable)
$ns.AddNamespace("sds","http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition")
$dsRefNode = $xml.SelectSingleNode("/sds:SharedDataSet/sds:DataSet/sds:Query/sds:DataSourceReference",$ns)
if ($dsRefNode -eq $null -or [string]::IsNullOrWhiteSpace($dsRefNode.InnerText)) {
Write-Warning "Skipping '$($f.Name)' — no <DataSourceReference> found."
continue
}
$dataSourceName = $dsRefNode.InnerText.Trim()
$dataSourceName = $dataSourceName -replace '^\s*/',''
$dataSourceName = ($dataSourceName -split '/')[ -1 ]
# ItemContent as Base64
$bytes = [System.IO.File]::ReadAllBytes($f.FullName)
$itemContentBase64 = [System.Convert]::ToBase64String($bytes)
# Build payload
$payload = [PSCustomObject]@{
Name = $datasetName
Description = "Imported from file: $($f.Name)"
DataSourceMappingInfo = @(
[PSCustomObject]@{
Name = $dataSourceName
DataSourceName = $dataSourceName
}
)
ItemContent = $itemContentBase64
}
$bodyJson = $payload | ConvertTo-Json -Depth 8 -Compress
Write-Host "`nPosting dataset: $datasetName (DataSourceName=$dataSourceName)"
$response = Invoke-RestMethod -Method POST -Uri $Endpoint -Headers $headers -Body $bodyJson -ErrorAction Stop
if ($response.Status -and $response.ApiStatus) {
Write-Host "✅ Created. PublishedItemId: $($response.PublishedItemId)"
} else {
Write-Warning "⚠️ API returned non-success status for '$datasetName'."
$response | ConvertTo-Json -Depth 6
}
}
catch {
$httpResp = $_.Exception.Response
if ($httpResp) {
$statusCode = $null
try { $statusCode = [int]$httpResp.StatusCode } catch { }
$sr = New-Object System.IO.StreamReader($httpResp.GetResponseStream())
$raw = $sr.ReadToEnd()
if ($statusCode -eq 409) {
Write-Warning "⏭️ Skipped: dataset '$datasetName' already exists (409 Conflict)."
continue
}
Write-Error "❌ Failed to add dataset '$datasetName': HTTP $statusCode"
Write-Host "--- Raw Server Response ---"
Write-Host $raw
} else {
Write-Error "❌ Failed to add dataset '$datasetName': $($_.Exception.Message)"
}
}
}
Result: All SSRS shared datasets are migrated and linked to Bold Reports data sources.
Step 4 — Migrate categories from SSRS folders and upload reports
Run the PowerShell script below to create Bold Reports categories based on SSRS folders and upload all .rdl reports into their respective categories.
<#
.SYNOPSIS
Re-creates SSRS folders as Bold Reports categories and
uploads all *.rdl* files into the proper category.
#>
#region ---------- USER SETTINGS ----------
$boldReportsBaseUrl = 'https://{yourdomain}' # ← no trailing slash
$bearerToken = 'Token'
$ssrsExportRoot = 'C:\SSRS_Out' # root of the Out-RsFolderContent dump
#endregion ------------------------------------
#---------- Helper: common headers ---------------------------------------------
$headers = @{
Authorization = "Bearer $bearerToken"
'Content-Type' = 'application/json'
}
#---------- Endpoints -----------------------------------------------------------
$siteBase = "$($boldReportsBaseUrl.TrimEnd('/'))/reporting/api/site/site1"
$listCategoriesEndpoint = "$siteBase/v1.0/items?ItemType=Category" # GET
$categoriesEndpoint = "$siteBase/v1.0/categories" # POST
$reportsEndpoint = "$siteBase/v1.0/reports" # POST
#---------- FUNCTIONS ----------------------------------------------------------
function Get-BoldCategoryId {
param([string]$CategoryName)
# 1. Try the in-memory cache first
if ($script:CategoryCache.ContainsKey($CategoryName)) {
return $script:CategoryCache[$CategoryName]
}
# 2. Fetch the list of existing categories
try {
$allCategories = Invoke-RestMethod -Method Get `
-Uri $listCategoriesEndpoint `
-Headers $headers
}
catch {
throw "Unable to fetch category list from server: $($_.Exception.Message)"
}
foreach ($cat in $allCategories) {
$script:CategoryCache[$cat.Name] = $cat.Id
}
if ($script:CategoryCache.ContainsKey($CategoryName)) {
return $script:CategoryCache[$CategoryName]
}
# 3. Still not found → create the category
$newCatBody = @{ Name = $CategoryName; Description = "Auto-created during import" } |
ConvertTo-Json -Depth 3
try {
$created = Invoke-RestMethod -Method Post `
-Uri $categoriesEndpoint `
-Headers $headers `
-Body $newCatBody
$script:CategoryCache[$created.Name] = $created.Id
Write-Host "✓ Created category '$CategoryName'" -ForegroundColor Green
return $created.Id
}
catch {
throw "Failed to create category '$CategoryName': $($_.Exception.Message)"
}
}
#---------- MAIN ---------------------------------------------------------------
$script:CategoryCache = @{}
Write-Host "Scanning for RDL files in $ssrsExportRoot …" -ForegroundColor Cyan
$rdlFiles = Get-ChildItem -Path $ssrsExportRoot -Recurse -Filter *.rdl
if (-not $rdlFiles) {
Write-Warning "No RDL files were found – nothing to upload."
return
}
foreach ($file in $rdlFiles) {
# Work out the category name (first folder level, or 'Unknown')
$relativePath = $file.FullName.Substring($ssrsExportRoot.Length).TrimStart('\')
$firstElem = ($relativePath -split '\\')[0]
$categoryName = if ($firstElem -eq $file.Name) { 'Unknown' } else { $firstElem }
# Ensure the category exists and grab its Id
try {
$categoryId = Get-BoldCategoryId -CategoryName $categoryName
}
catch {
Write-Warning $_
continue
}
# Read RDL and convert to Base-64
try {
$itemB64 = [Convert]::ToBase64String([IO.File]::ReadAllBytes($file.FullName))
}
catch {
Write-Warning "Could not read '$($file.FullName)': $($_.Exception.Message)"
continue
}
# Build request body
$bodyObj = @{
Name = $file.BaseName
Description = "Imported from SSRS ($relativePath)"
CategoryId = $categoryId
Tags = @()
ServerPath = ""
IsPublic = $false
DataSetMappingInfo = @()
DataSourceMappingInfo = @()
ItemContent = $itemB64
}
$jsonBody = $bodyObj | ConvertTo-Json -Depth 10
try {
Invoke-RestMethod -Method Post `
-Uri $reportsEndpoint `
-Headers $headers `
-Body $jsonBody
Write-Host "✓ Uploaded report '$($file.BaseName)' to category '$categoryName'" -ForegroundColor Green
}
catch {
$resp = $_.Exception.Response
if ($resp -and $resp.StatusCode.value__ -eq 409) {
Write-Host "⚠ Report '$($file.BaseName)' already exists – skipping." -ForegroundColor Yellow
}
else {
Write-Warning "Failed to upload '$($file.BaseName)': $($_.Exception.Message)"
}
}
}
}
Result: Categories are created in Bold Reports to mirror SSRS folder names. Every RDL is uploaded to its category.
Additional Resources
