Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Does someone have a powershell script to change the data source?
For example I want deploy my report to powerBI reporting service from Test to Productios therefore i need to change the datasource from
OTA-DWH-01\Test to OTA-DWH-02\Production
I have a lot of reports so doing it manually will take a lot of time. I now have to open each report indivually and change the datasource.
I've tried the powershell script mentioned in this thread but I will always get a 400 bad request.
I've also tried using the API (https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/PowerBIReports/UpdatePowerBIReportDataSource) and will also get 400 bad request if I touch the connection string.
If anyone has any other ideas, I'm open to trying as in my organization we have multiple environments I would like to deploy to automatically.
Is there any solution to this problem? We are trying to edit a PBIRS DataSource connecting to an on-premise SQL Server database, using the UpdatePowerBIReportDataSource method.
No matter what we edit (database name, server or user), we get a 400 bad request...
Our PBIRS is 15.0.1102.371 (Mai 2019).
To my knowledge, it is only possible with SSAS-based sources. It's certainly not possible with SQL Server databases.
You're able to attempt this with your report by: "Manage"-->"Data sources"-->"Connection string". Try to edit the connection string. It's possible in SSAS but nothing else I've ran into.
@Anonymousthanx for the quick reply! I have no clue (and no understanding) why this function is limited to SSASconnections only... What were they thinking??? Let's hope it is going to be fixed soon...
Does this also work for power bi report server? because when i look into the script it says i need to get the source report info from powerbi service.
(Responding to fbeekvel)
Everything I've tried to change the ReportServer connection string, including all 3 articles you've attached. The connection string is local to the file, and the file only. The connection string is read-only in the ReportServer.
I'd love to be proven wrong, but I don't think it's possible. One possible way is to zip the file and change the connection string text, then zip it back up. That's not enterprise-grade though so I haven't pursued it.
#Install-Module -Name ReportingServicesTools
<# Set parameters #>
$ReportServerURI = 'https://MyReportServer/reports'
$MyReport = "/Folder/Reportname"
$MyUserName = "domain\account"
$MyPassword = "password"
try {
Write-Verbose "Creating a session to the Report Server $ReportServerURI"
# establish session w/ Report Server
$session = New-RsRestSession -ReportPortalUri $ReportServerURI
Write-Verbose "Created a session to the Report Server $ReportServerURI"
}
catch {
Write-Warning "Failed to create a session to the report server $ReportServerURI"
Return
}
try {
Write-Verbose "Getting the datasources from the pbix file for updating"
# get data source object
$datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$MyReport"
Write-Verbose "Got the datasources for updating"
}
catch {
Write-Warning "Failed to get the datasources"
Return
}
try {
Write-Verbose "Updating Datasource"
foreach ($dataSource in $datasources) {
$dataSource.CredentialRetrieval = 'Store'
$dataSource.ConnectionString = "Data Source=SSASserver;Initial Catalog=SSASdatabase"
$dataSource.IsConnectionStringOverridden = $True
$dataSource.DataModelDataSource.Username = $MyUserName
$dataSource.DataModelDataSource.Secret = $MyPassword
}
Write-Verbose "Updating the data source for the report $PBIXName"
# update data source object on server
Set-RsRestItemDataSource -WebSession $session -RsItem "$MyReport" -RsItemType PowerBIReport -DataSources $datasource
}
catch {
Write-Warning "Failed to set the datasource"
Return
}
Write-Verbose "Completed Successfully"
this one is for updating a connectionstring to a SSAS database using stored credentials.
This maybe be a stupid question (powershell noob) but how do i do this for example if i have a folder named 'mypowerbifolder' where i have 20 pbix files in C:\Users\Tanako123\Documents\mypowerbifolder and want to run this script for all my pbix files in this folder.
I tried to google this how to do this, which should be pretty easy i think but i couldnt understand the logic
it should be a combination of get-childitem and for each?
thanks in advance!
I don't know if there is an existing script for this however it should be pretty straightforward assuming your using Shared DataSources. All you would need to do is issue a PUT request to the following REST API for each of your reports:
http://<server>/reports/api/v2.0/Reports(ID/Path)/DataSources
As for the payload you just need to update the path to the new DataSource. You can refer to the API docs here:
https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/Reports/SetReportDataSource
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
3 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
5 | |
5 | |
4 | |
4 | |
3 |