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.

      • Sania198's avatar
        Sania198
        Frequent Visitor
        3 years ago

        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