Forum Discussion
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...
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
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
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.
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 name2. 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
