powerbi-report-server rest-api pbix data-source csv powershell
1 Topic
How to batch update CSV data source paths in deployed Power BI Report Server .pbix files?
Hi all, I'm currently working with Power BI Report Server and have over 300 deployed .pbix reports that connect to CSV files via UNC paths like: \\hostnameA\folder\subfolder\file.csv I need to batch update all these paths to: \\hostnameB\folder\subfolder\file.csv 🔍 What I've tried: I used the Report Server REST API (/PowerBIReports(id)/DataSources) to list and patch the data sources. For embedded .pbix reports, I can retrieve the ConnectionString, but fields like Name, Path, and DataSourceType are null. When I try to send a PATCH request with updated connection strings, I get: 「400 Bad Request」 ❓My Questions: Is there an official or recommended way to update CSV file paths in deployed .pbix reports in bulk? Is it possible to do this without manually opening each .pbix in Power BI Desktop and re-saving? Would converting the embedded data sources to shared data sources (.rsds) help with future automation? Thanks in advance for any advice or workaround! PowerShell Script #script $server = "http://hostname" # Do not include /reports $apiBase = "$server/reports/api/v2.0" $oldHost = "\\hostnameA" $newHost = "\\hostnameB" # Get all Power BI reports $reports = Invoke-RestMethod -Uri "$apiBase/PowerBIReports" -UseDefaultCredentials foreach ($report in $reports.value) { $reportId = $report.Id $reportName = $report.Name # Get data sources for the report $dataSourcesUrl = "$apiBase/PowerBIReports($reportId)/DataSources" $dataSources = Invoke-RestMethod -Uri $dataSourcesUrl -UseDefaultCredentials $updateNeeded = $false $updateDetails = @() foreach ($ds in $dataSources.value) { $oldConnStr = $ds.ConnectionString # Check if old hostname is present if ($oldConnStr -like "*$oldHost*") { $newConnStr = $oldConnStr -replace [regex]::Escape($oldHost), $newHost Write-Host "`n[$reportName] Updating connection string:" -ForegroundColor Cyan Write-Host "Original: $oldConnStr" Write-Host "Updated : $newConnStr" -ForegroundColor Yellow # Prepare updated datasource object $updateDetails += @{ DataSourceType = $ds.DataSourceType ConnectionString = $newConnStr Name = $ds.Name Path = $ds.Path } $updateNeeded = $true } } if ($updateNeeded) { $body = @{ value = $updateDetails } | ConvertTo-Json -Depth 10 # Send PATCH request to update the data source $patchUrl = "$apiBase/PowerBIReports($reportId)/DataSources" Invoke-RestMethod -Method Patch -Uri $patchUrl -UseDefaultCredentials -Body $body -ContentType "application/json" Write-Host "✔ Data sources updated for report [$reportName]" -ForegroundColor Green } }Solved1.3KViews0likes3Comments