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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
udhaya2089
Frequent Visitor

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

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 ?

1 ACCEPTED SOLUTION
ibarrau
Super User
Super User

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

View solution in original post

3 REPLIES 3
udhaya2089
Frequent Visitor

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

Hi @udhaya2089 ,

I am working on similar requirement and facing issues achieving this . would it be possible to share your script here please.

ibarrau
Super User
Super User

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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