How to Pull Parameters (Server & Database) programmatically from SQL Database via PowerShell Script

[Sania198]

Hi,

I can successfully update the parameters using: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-parameters-in-group , however I need help in updating Parameters (Server & Database) programmatically pulling from SQL Database and add it to the already Published Power BI Dataset.

Table with Server & Database details is already created in SQL DB, using Rest API or PS Script I want them to update to the dataset automatically (without giving hardcoded variables(values)) which is already published to Power BI Service.

Exactly I'm looking for this option: https://youtu.be/MJVSu47iMmo?t=413 from (6:53 to 7:06) via PS Script 

Thanks in Advance! 

[lbendlin]

The video describes how to do this via PowerShell.  What have you tried and where are you stuck ?

[Sania198]

@lbendlin 

I can update parameters when I give a hardcoded values like below and use it in the below script

$parameterValue1 = "Testing"

$parameterValue2 = "Local.Check"

$datasetParametersUrl = "groups/$workspaceId/datasets/$datasetId/Default.UpdateParameters"

$body = @{

    updateDetails = @(

        @{

            name = "Server"

            newValue = $parameterValue2

        }

        @{

            name = "Database"

            newValue = $parameterValue1

        }

    )

}

$jsonPostBody = $body | ConvertTo-Json

Invoke-PowerBIRestMethod -Url:$datasetParametersUrl -Method:Post -Body:$jsonPostBody `

                         -ContentType:'application/json'

Write-Host "Successfully updated specified parameters" -ForegroundColor Green

Now I have a SQL DB table which has all the Server and Database details, I need help in writing PS script where the Server & Database details need to pull from DB and add it to the published dataset in Power BI Service

I am succesfully in pulling the SQL DB table data via PS Script, however don't know how to use that table to automatically add it to the dataset in PBI Service which has Server and Database details

It would be very helpful if you can help me with the sample script to use the DB table data to update the parameters automatically to the published dataset 

[Sania198]

I tried different loops but ending with below error even though the authentication is going good:

My modified script:

# query to show changes

$Query = '

SELECT TOP (1000) [DATAB]

      ,[Server]

      ,[database]

  FROM [dbo].[vw_BIWhouse_Info]

'

#RestAPICommand

$SQLCmd = Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -Query $Query -Username $SqlAuthLogin -Password $SqlAuthPw

foreach ($DB in $SQLCmd) {

       $DB.DATAB  | Where-Object {$DB.database -eq 'client'}

       $DB.Server | Where-Object {$DB.database -eq 'client'}

       $UD = $DB.DATAB  | Where-Object {$DB.database -eq 'client'}

       $US = $DB.Server | Where-Object {$DB.database -eq 'client'}

       $parameterValue1 = $UD

       $parameterValue2 = $US

}

   Connect-PowerBIServiceAccount

 # Specifed workspace and dataset where the current dataset is stored

$WorkspaceID = "00000000-0000-0000-0000-000000000000"

$DatasetID = "00000000-0000-0000-0000-000000000000"

$datasetParametersUrl = "groups/$WorkspaceID/datasets/$DatasetID/Default.UpdateParameters"

$body = @{

     updateDetails = @(

 @{

     name = "Server"

     newValue = $parameterValue2

 }

 @{

     name = "Database"

     newValue = $parameterValue1

 }

)

}

 $jsonPostBody = $body | ConvertTo-Json

 Invoke-PowerBIRestMethod -Url:$datasetParametersUrl -Method:Post -Body:$jsonPostBody -ContentType:'application/json'

 Disconnect-PowerBIServiceAccount

However, when I use the below script it updates the parameters pulling from DB which matches with CLIENT object but it iterates through each DB ID and throws below error for IDs which doesn't match

Script:

Connect-PowerBIServiceAccount

  $WorkspaceID = "00000000-0000-0000-0000-000000000000"

  $DatasetID = "00000000-0000-0000-0000-000000000000"

$Query = '

SELECT TOP (1000) [DATAB]

      ,[Server]

      ,[database]

  FROM [dbo].[vw_BIWhouse_Info]

'

$SQLCmd = Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -Query $Query -Username $SqlAuthLogin -Password $SqlAuthPw

foreach ($DB in $SQLCmd) {

       $DB.DATAB  | Where-Object {$DB.database -eq 'client'}

       $DB.Server | Where-Object {$DB.database -eq 'client'}

       $UD = $DB.DATAB  | Where-Object {$DB.database -eq 'client'}

       $US = $DB.Server | Where-Object {$DB.database -eq 'client'}

       $parameterValue1 = $UD

       $parameterValue2 = $US

  $datasetParametersUrl = "groups/$WorkspaceID/datasets/$DatasetID/Default.UpdateParameters"

  $body = @{

       updateDetails = @(

   @{

       name = "Server"

       newValue = $parameterValue2

   }

   @{

       name = "Database"

       newValue = $parameterValue1

   }

  )

  }

   $jsonPostBody = $body | ConvertTo-Json

  Invoke-PowerBIRestMethod -Url:$datasetParametersUrl -Method:Post -Body:$jsonPostBody -ContentType:'application/json'

}

Disconnect-PowerBIServiceAccount

I only want to pull the database and Server details which matches "CLIENT" and updates it to the PBI Service dataset

[lbendlin]

Not sure why you are using separate loops - your overall process is not clear to me.

Beware - 429 errors mean you are running too many API calls in too short of a timeframe.

[Sania198]

@lbendlin 

I am ready to modify the script if needed, can you please help me with sample script

1. I have a SQL DB table with Server and Database details for different clients
For example, we have A,B,C,D as clients name

2. I want to use that SQL query and pull those parameters (Server and Database) details of specific client of "C" 

3. Then it should update to the Power BI dataset I specify

That's my requirement 

[Sania198 · Accepted solution]

I resolved the issue! 🙂

Need to pass If condition in between Foreach loop to get the specific details and to update it to the dataset.

Thanks for your responses @lbendlin