cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tanako1991 Regular Visitor
Regular Visitor

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
Moderator mgmeyer
Moderator

Re: Powershell script to change Datasource Test to Production

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

CasperLehmann Frequent Visitor
Frequent Visitor

Re: Powershell script to change Datasource Test to Production

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
tanako1991 Regular Visitor
Regular Visitor

Re: Powershell script to change Datasource Test to Production



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.

CasperLehmann Frequent Visitor
Frequent Visitor

Re: Powershell script to change Datasource Test to Production

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

Re: Powershell script to change Datasource Test to Production

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

fbeekvel
Advisor

Re: Powershell script to change Datasource Test to Production

I'm pretty sure It works. I have a working script somewhere, I'll see if I can post it tomorrow.
Highlighted
fbeekvel
Advisor

Re: Powershell script to change Datasource Test to Production

#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"

fbeekvel
Advisor

Re: Powershell script to change Datasource Test to Production

this one is for updating a connectionstring to a SSAS database using stored credentials.