Search code examples
azure-pipelinessqlcmdazure-powershellazure-pipelines-build-taskinvoke-sqlcmd

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

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

enter image description here

or deploy SQL using DACPAC or SQLCMD

enter image description here

or even execute PowerShell on remote machines

enter image description here

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

Solution

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

    enter image description here

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