How to execute SQL in a PowerShell script as part of a Visual Studio Team Services build task?

Question

How to execute SQL against an Azure SQL database as part of a PowerShell script in a VSTS build task in a hosted agent?

There are many tasks available for build definitions: we can execute a PowerShell script

or deploy SQL using DACPAC or SQLCMD

or even execute PowerShell on remote machines

but how to simply execute SQL as part of a PowerShell script? Invoke-Sqlcmd isn't available as part of the 'Azure PowerShell' task.

I guess it would be possible to remote desktop to an app service, install the SQL Server related bits there and use 'PowerShell on Target Machines' but I feel there has to be a simpler way - perhaps I'm just missing something obvious

Answer 1

You can use Azure SQL Database Deployment task, there are SQL Script File and Inline SQL Script types.

Note: You can use Hosted agent, but not Hosted VS 2017 agent.

Answer 2

but how to simply execute SQL as part of a PowerShell script? Invoke-Sqlcmd isn't available as part of the 'Azure PowerShell' task.

I think we can use this PowerShell script to run SQL with Azure Powershell for Azure SQL database:

$connectionString = "Data Source=jasontest321.database.windows.net;Initial Catalog=jasonsql;User ID=jason;Password='password';Connection Timeout=90"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$query = "insert into test1 values (3) ; ;" 
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
$connection.Open()
$command.ExecuteNonQuery()
$connection.Close()

Here is my test:

Also we can use Azure portal Query editor to query the table, like this:

Answer 3

Invoke-SqlCmd becomes available after you run:

Install-Module -Name SqlServer -Force

Tested on windows-2019 and ubuntu-18.04 (latest at this time).