Execute SQLCMD.exe from PowerShell in an Azure DevOps Release Pipeline

Question

I'm porting our deployment infrastructure to Azure DevOps (formerly Visual Studio Team Services), but I've run into an issue I can't find any good solutions to.

We're using EF Core Migrations, and to work around some unrelated issues we need to dynamically wrap SQL scripts in SQLCmd, which is easy using PowerShell.

However, when executing our script as a regular PowerShell step in the release pipeline, it fails because SQLCMD.exe is not available. Not so surprising - but I also can't find any documented way of installing it.

Is there a tool installer or some similar ready-made component that will let me execute a PowerShell script that calls out to SQLCMD.exe (via Invoke-SqlCmd) as part of an Azure Devops Pipeline? If not, what's the easiest way to accomplish this anyway?

Answer 1

Haven't tried this myself, but are you allowed to install Powershell modules for the current user on a hosted agent? Because Invoke-SqlCmd is part of the SqlServer module, which can be installed from the Powershell gallery with:

Install-Module -Name SqlServer -Scope CurrentUser