Changing the Datasource from one SQL Server to Another Server for 100+ of Power BI Reports.

[udhaya2089]

We have created 100+ Reports Power BI Reports and published on the Power BI App. The datasource for this reports is on-premises SQL Server. Now we are migrating the 2016 SQL to 2019 SQL and changed the name of the server as well. Right now I need to change my all datasource into the new servers. I don't want to go for the manual process. How can I change this datasource on a single acitivity like an automated one ?

[udhaya2089]

Thanks. It really helped. I tried to create a power shell script to update on group, but ended with some error. So I used the API to manually for around 60 reports. Thank you so much..

[ibarrau · Accepted solution]

Hi. This might be a tricky thing to do. Usually when everything is at cloud you can do it. It might work if the reports only have one single source (the sql server to update) because otherwise it might be complicated to build the request.

The only way of doing this is building a script using the Power Bi Rest API. There is a request to update the data sources for a specific dataset. You can have the list of datasets to change the sever name and loop them posting this request: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-datasources-in-group

Due to the fact that this might be on premise, there might be things with the gateway. Best case scenario would be just adding the source. Before starting I would suggest doing one without the loop to check if you must edit any credentials or create the source at gateway. Once that one is working continue with the script for all of them.

Developer/scripting skills are required for this task (any language programing)

I hope that helps,