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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 301 members 3,110 guests
Please welcome our newest community members: