Forum Discussion

bolagam1986's avatar
bolagam1986
Frequent Visitor
4 years ago

Need help on Migrating SSRS rdl files to Power BI Service using PowerShell script

I have multiple rdl files using On-premise shared SQL db connection. My requirement is publish these rdl files into Power BI Service by changing the database connection from On-premise SQL db to Azure Serverless SQL db using PowerShell.
I have tried below PowerShell script(referred this link https://github.com/microsoft/powerbi-powershell/issues/218 ) to publish single rdl file into Power BI Service.

This script is working fine when i change the data source connection to Azure Serverless SQL db from On-premise SQL db manually opening the rdl file in Power BI Report builder and saved report locally("C:\Reports\Summary.rdl") but can't migrate with existing On-premise SQL db connection and getting below error message because Paginated Reports don't support shared data source and need to convert the connection to embedded connection.

 

So I need some help in below PowerShell script to change the data source connection to Azure Serverless SQL db and convert shared data source connection to embedded connection so that i can publish the report into Power BI Service.


Error Message:

{"error":{"code":"PaginatedReportAuthoringError","pbi.error":{"code":"PaginatedReportAuthoringError","parameters":{},"details":[{"code":"RdlWorkloadErrorCode"
,"detail":{"type":1,"value":"PaginatedReportAuthoringError"}},{"code":"RdlWorkloadErrorMessage","detail":{"type":1,"value":"Report contains unsupported shared data source: eton_rei_data."}}
,{"code":"PaginatedReportAuthoringError","detail":{"type":1,"value":"Report contains unsupported shared data source: eton_rei_data."}},{"code":"HostMode","detail":{"type":1,"value":"AutoPremium"}},{"code":"CapacityObjectId","detail":{"type":1,"value":"840204A1-4233-4CE6-B550-84F02691A369"}}],"exceptionCulprit":1}}}


PowerShell Script:

Connect-PowerBIServiceAccount
$token = Get-PowerBIAccessToken -AsString
$headers = @{
'Authorization' = $token
}

$path_to_rdl = "C:\Reports\Summary.rdl"

# RDL only supports "Abort" Or "Overwrite" and will fail with anything else
$conflict = 'Abort'
$workspaceID = '213aa0aa-4576-4601-b249-04583c484aa2'

$form = @{ file = Get-Item $path_to_rdl }
[string]$itemname = Split-Path $path_to_rdl -Leaf

$response = Invoke-RestMethod -Uri "https://api.powerbi.com/v1.0/myorg/groups/$workspaceID/imports?datasetDisplayName=$itemname&nameConflict=$conflict"-Method POST -ContentType "multipart/form-data" -Form $form -Headers $headers

3 Replies