Forum Discussion

Sania198's avatar
Sania198
Frequent Visitor
3 years ago
Solved

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

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 & Dat...
  • Sania198's avatar
    Sania198
    3 years ago

    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 

lbendlin's avatar
lbendlin
Super User
3 years ago

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

  • Sania198's avatar
    Sania198
    Frequent Visitor
    3 years ago

    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's avatar
      Sania198
      Frequent Visitor
      3 years ago

      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's avatar
        lbendlin
        Super User
        3 years ago

        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.