Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Powershell script to change Datasource Test to Production

 

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.

14 REPLIES 14
cchen407etr
New Member

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).

Anonymous
Not applicable

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...

CasperLehmann
Frequent Visitor

There is a Powershell script for this under the Azure-Samples repo. Just remember that you need to register an App to get a Client ID to use with it. https://github.com/Azure-Samples/powerbi-powershell/blob/master/rebindReport.ps1
Anonymous
Not applicable



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.

I don't know how you connect to the report server, there might be differences. But please report back if you do try it out. I would like to know.
Anonymous
Not applicable

(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.

Anonymous
Not applicable

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'm pretty sure It works. I have a working script somewhere, I'll see if I can post it tomorrow.
mgmeyer
Power BI Team
Power BI Team

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors