Forum Discussion
Solved
How to use SQL query data to create a Power BI Workspaces via PowerShell or Rest API Script
Hi,
- I want to create a Power BI workspaces by referencing to the view created in our SQL DB.
- I am successful in pulling the data by connecting to SQL DB where we have a table with DatabaseName, by using that DatabaseName from DB PBI workspaces should be created if the workspace doesn't exists.
- If the workspace already exists then based on the workspace state like (if the workspace is in DELETE State then it should be activated) or If it is already in ACTIVE state then no operation should be performed.
However, when trying it with below PS Script I am getting errors. Below is the error screenshot
PowerShell Script I'm using:
$Query = '
SELECT TOP (3) [DatabaseName]
FROM [dbo].[vw_ListToAddToPBIws]
'
$SQLFile = Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -Query $Query -Username $SqlAuthLogin -Password $SqlAuthPw
$SQLFile = Select-Object
Write-Host
Connect-PowerBIServiceAccount | Out-Null
$workspaceName = $SQLFile
$workspace = Get-PowerBIWorkspace -Name $workspaceName
if($workspace) {
Write-Host "The workspace named $workspaceName already exists" -ForegroundColor Green
}
else {
Write-Host "Creating new workspace named $workspaceName" -ForegroundColor DarkBlue
$workspace = New-PowerBIGroup -Name $workspaceName
}
I really appreciate if someone can help me with the above ask.
Thanks in Advance!
$SQLFile returns an object (the table of the rows for your query result). Now you need to run a ForEach on this to address the individual workspaces.
3 Replies
$SQLFile returns an object (the table of the rows for your query result). Now you need to run a ForEach on this to address the individual workspaces.
