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?

Are you using the hosted agent or a private agent? – Daniel Mann Oct 16 '18 at 00:57 — Daniel Mann, Oct 16 '18 at 00:57
Hosted agents (Windows with VS2017). – Tomas Aschan Oct 16 '18 at 07:06 — Tomas Aschan, Oct 16 '18 at 07:06

score 6 · Accepted Answer · answered Oct 16 '18 at 10:36

6

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

answered Oct 16 '18 at 10:36

Leon Bouquiet

4,159
3
25
36

This is what I eventually ended up doing. It turns out the `sqlcmd.exe` is actually already on the machine (but not in `$env:PATH`) so all I had to do was to install the PS module (and port my script to use it everywhere; I had one direct reference `sqlcmd.exe` that I replaced with `Invoke-SqlCmd`...). Thanks! – Tomas Aschan Oct 17 '18 at 10:02
@TomasAschan I want to add something that might help someone. In my case the system variable was already set and I used `Invoke-Sqlcmd` from the beginning in my script. Nevertheless, the task that uses this cmdlet in Azure Pipeline told me that `Invoke-Sqlcmd` cannot be found. The reason for this was: After installing cmdlet, I did not restart the computer (especially BuildAgent), which is why BuildAgent did not notice that there was a new module for powershell. After the restart everything worked fine. I used `Install-Module -Name SqlServer -Scope AllUsers` in order to install SqlServer. – deralbert Apr 22 '20 at 18:27

score 0 · Answer 2 · answered Oct 16 '18 at 10:17

You could try to create a package to install the needed tools on the agent, since that is now possible. You could use a Chocolatey task to run a package from it

There is an (old) chocolatey package available that you could try: https://chocolatey.org/packages?q=SQLCMD

Can't you use normal sql scripts via one of the available extensions? See here or here

Ofcourse, using a self-hosted agent could be an option, then you can install anything you want.

score 0 · Answer 3 · answered Feb 01 '19 at 06:34

0

This one worked for me

Install-Module -Name SqlServer -Force -AllowClobber

Not sure about -AllowClobber though, on my local PC the command complained about something without it and I didn't check on Azure tbh.

answered Feb 01 '19 at 06:34

Andrey Stukalin

5,328
2
31
50

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

3 Answers3