UpdateDatasourcesInGroup on a v3 datasource / data...

caiken · ‎05-13-2020

Hi all -- I have a need to change the server, database, username, and password for Datasets on a Power BI workspace with attached premium capacity, using Azure Power BI Embedded. All data sources are Azure PaaS SQL.

Historically, I have used SetAllDatasetConnectionsInGroup to change the server and database, and then used UpdateDatasource to change the username and password. This has worked great -- but I knew that SetAllDatasetConnectionsInGroup was depreciated and it would eventually break -- and that day has come.

It no longer works with newer Power BI files that have the "v3" data sources enabeld, called "enhanced metadata) in Power BI Desktop (see https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enhanced-dataset-metadata ). The SetAllDatasetConnectionsInGroup now returns the error PowerBIUpdateMetadataUnsupportedModelWithV3DataSourceException.

To try to fix this, I moved to using the UpdateDatasourcesInGroup, but this doesn't seem to work. It changes the server and database as shown in the Power BI portal, but when I access the report, it shows an error, and that error contains the OLD servername and database.

So -- what is the proper way to change the server and database on a v3 datasource usign the REST API when the datasource is conencted via DirectQuery to Azure PaaS SQL?

BTW -- I tried many workarounds, including using parameters for servername/database (result: parameters not permitted on the PowerBI service for DirectQuery SQL), editing the PBIX file before uploading (not possible as connection string is embedded in compressed data in the PBIX for v3 data sources), using a PBIT (not allowed in REST upload), etc.

Help -- and thanks in advance.

TedPattison · ‎10-30-2020

This works for me...

public static void UpdateSqlDatabaseConnectionString(Guid WorkspaceId, string DatasetId, 
                                                     string Server, string Database) {

  var pbiClient = TokenManager.GetPowerBiClient(requiredScopes);

  Datasource targetDatasource = 
             pbiClient.Datasets.GetDatasourcesInGroup(WorkspaceId, DatasetId).Value.First();

  string currentServer = targetDatasource.ConnectionDetails.Server;
  string currentDatabase = targetDatasource.ConnectionDetails.Database;

  if (Server.ToLower().Equals(currentServer.ToLower()) && 
      Database.ToLower().Equals(currentDatabase.ToLower())) {
    Console.WriteLine("New server and database name are the same as the old names");
    return;
  }

  DatasourceConnectionDetails connectionDetails = new DatasourceConnectionDetails {
    Database = Database,
    Server = Server
  };

  UpdateDatasourceConnectionRequest updateConnRequest =
    new UpdateDatasourceConnectionRequest {
      DatasourceSelector = targetDatasource,
      ConnectionDetails = connectionDetails
    };

  UpdateDatasourcesRequest updateDatasourcesRequest = 
                           new UpdateDatasourcesRequest(updateConnRequest);
  
  pbiClient.Datasets.UpdateDatasourcesInGroup(WorkspaceId, DatasetId, updateDatasourcesRequest);

}