topic Re: Powershell script to change Datasource Test to Production in Report Server

Powershell script to change Datasource Test to Production

Anonymous — Fri, 30 Nov 2018 13:00:44 GMT

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.

Re: Powershell script to change Datasource Test to Production

mgmeyer — Fri, 30 Nov 2018 22:43:07 GMT

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

Re: Powershell script to change Datasource Test to Production

fbeekvel — Sat, 01 Dec 2018 09:22:49 GMT

Here are some links:

http://byobi.com/2018/04/programmatically-deploy-power-bi-reports-to-power-bi-report-server/

https://sqldbawithabeard.com/2018/08/21/deploying-to-a-power-bi-report-server-with-powershell/

https://www.blue-granite.com/blog/power-bi-report-server-devops

Re: Powershell script to change Datasource Test to Production

CasperLehmann — Mon, 03 Dec 2018 12:08:06 GMT

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

Re: Powershell script to change Datasource Test to Production

Anonymous — Mon, 03 Dec 2018 15:27:21 GMT

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.

Re: Powershell script to change Datasource Test to Production

CasperLehmann — Tue, 04 Dec 2018 09:58:31 GMT

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.

Re: Powershell script to change Datasource Test to Production

Anonymous — Wed, 05 Dec 2018 22:55:49 GMT

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

Re: Powershell script to change Datasource Test to Production

fbeekvel — Wed, 05 Dec 2018 22:57:53 GMT

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

Re: Powershell script to change Datasource Test to Production

fbeekvel — Thu, 06 Dec 2018 17:16:41 GMT

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

Re: Powershell script to change Datasource Test to Production

fbeekvel — Thu, 06 Dec 2018 17:18:30 GMT

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

Re: Powershell script to change Datasource Test to Production

Anonymous — Thu, 06 Dec 2018 17:54:15 GMT

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!

Re: Powershell script to change Datasource Test to Production

cchen407etr — Tue, 12 Feb 2019 22:55:26 GMT

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.

Re: Powershell script to change Datasource Test to Production

imichalarias — Thu, 11 Jul 2019 07:11:48 GMT

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

Re: Powershell script to change Datasource Test to Production

Anonymous — Thu, 11 Jul 2019 13:30:47 GMT

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.

Re: Powershell script to change Datasource Test to Production

imichalarias — Fri, 12 Jul 2019 10:56:09 GMT

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