Example code for Connecting from a Symantic Model in a PowerBi Workspace to a DataSource (SQL Server) - Stack Overflow

admin管理员组

文章数量:1356763

I'm using the PowerBIClient API for creating resources in the PowerBI environment. I'm trying to connect my uploaded Symantic Model to a SQL Server (All in Azure)

I have tried to set the Connection on the DataSet with the pbiClient.Datasets.SetAllDatasetConnectionsAsync, but there I'm missing something.

Trying to first create a DataSource: with CreateDatasourceAsync but there also I have a lack on information. I really want to connect based on managed Identity...

Does someone have a working example how to create a datasource the correct way?

For now I have:

 public async Task SetDataConnection(Guid groupId, string datasetId, string reportingSqlServer, string reportingSqldatabase)
 {
     var pbiClient = await GetPowerBIClient() ?? throw new PowerBiException(PowerBiClientIsNull);

     var request = new UpdateMashupParametersRequest()
     {
         UpdateDetails = [
                 new UpdateMashupParameterDetails() { Name = "DatabaseServer", NewValue = reportingSqlServer },
                 new UpdateMashupParameterDetails() { Name = "DatabaseName", NewValue = reportingSqldatabase }
            ]
     };

     await pbiClient.Datasets.UpdateParametersInGroupAsync(groupId, datasetId, request);

     await PatchSqlDatasourceCredentials(pbiClient, groupId, datasetId, "xxxx", "xxxx");
 }

public async Task PatchSqlDatasourceCredentials(PowerBIClient pbiClient, Guid groupId, string datasetId, string sqlUserName, string sqlUserPassword)
{
    var datasources = (await pbiClient.Datasets.GetDatasourcesInGroupAsync(groupId, datasetId)).Value;

    // find the target SQL datasource
    foreach (var datasource in datasources)
    {
        if (datasource.DatasourceType.ToLower() == "sql")
        {
            // get the datasourceId and the gatewayId
            var datasourceId = datasource.DatasourceId;
            var gatewayId = datasource.GatewayId;

            if (gatewayId is null || datasourceId is null)
            {
                throw new Exception("GatewayId or DatasourceId is null");
            }

            // Create UpdateDatasourceRequest to update Azure SQL datasource credentials
            var req = new UpdateDatasourceRequest
            {
                CredentialDetails = new CredentialDetails(
                new BasicCredentials(sqlUserName, sqlUserPassword),
                PrivacyLevel.None,
                EncryptedConnection.NotEncrypted)
            };

            // Execute Patch command to update Azure SQL datasource credentials
            await pbiClient.Gateways.UpdateDatasourceAsync(gatewayId.Value, datasourceId.Value, req);
        }
    }
}

Thanks in advance Bart

本文标签：