Query from powershell to SQL

[Canale]

Hello

My problem is next:

I need make a query to read a database from powershell to sqlserver and after that up to powerbi online

any ideas about that.

this is my code

$SQLServer = "server"
$SQLDBName = "prueba"
$uid ="remoto"
$pwd = " "
$query = "SELECT * FROM table WHERE Column = 'id'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; User ID = $uid; Password = $pwd;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

 $endpoint = "https://api.powerbi.com/beta/1b682faf-ba6c-46c6-a1e7-74ebc6a55b1f/datasets/8cb9acd2-13a8-486d-93bc-1..."

$payload = @{
"FC210-2" =  "This put de value  I call from sql"
}
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))

[v-chuncz-msft · Accepted solution]

@Canale,

You may refer to the following post.

https://community.powerbi.com/t5/Developer/Stream-Dataset-for-local-SQL-Server-Database-Query/td-p/1...

[Canale]

Hello

My problem is next:

I need make a query to read a database from powershell to sqlserver and after that up to powerbi online

any ideas about that.

this is my code

$SQLServer = "server"
$SQLDBName = "prueba"
$uid ="remoto"
$pwd = " "
$query = "SELECT * FROM datos WHERE Column = 'id'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; User ID = $uid; Password = $pwd;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

 $endpoint = "https://api.powerbi.com/beta/1b682faf-ba6c-46c6-a1e7-74ebc6a55b1f/datasets/8cb9acd2-13a8-486d-93bc-1..."

$payload = @{
"FC210-2" =  "This put de value from sql"
}
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))