A lot of the projects I work on are not code-first projects. A database already exists in most of the projects and database scripts also need to be executed during deployment of changes in order to ensure a working environment.
It is possible to build a database project into a DACPAC file and use this file to deploy new databases or changes in existing databases, but sometimes a plain and simple SQL script is easier to use and maintain.
The new build and release management environment in Azure DevOps and Azure DevOps On-Prem enables us to embed custom tasks into a build or release pipeline. I decided to give it a try and build a custom build/release management task that can execute a SQL script on a given database.
Azure DevOps On-Prem Cross Platform Command Line Interface
The tooling that enables users to upload customs tasks (and more) is the Azure DevOps Cross Platform Command Line Interface (TFX). This is a GitHub project and documentation is available at https://github.com/Microsoft/tfs-cli.
In order to access your Azure DevOps or Azure DevOps On-Prem environment using TFX, you need to create a personal access token or use basic authentication. Read the Using Personal Access Tokens to access Visual Studio Online post to find out how to create an access token.
The “tfx build task create” command will create a custom task template that you can change to suit your needs. It will create a new folder containing a JSON file, a sample Node.js script, a sample PowerShell script and an icon.
Once you have changed the template to suit your needs, you can install it in the collection that you are logged on to (with the tfx log-on command) by running the command “tfx build tasks upload –task-path <path to your custom task definition>”.
A custom task definition consists of a task description (JSON file), one or more PowerShell scripts and optional custom DLLs.
The task description file describes and identifies the task, contains versioning information, describes the required capabilities of the agent it can run on, and describes input variables and the way the task is executed (PowerShell or Node.js). The Custom Build Tasks in TFS 2015 post contains some basic information on writing custom build tasks.
Run a SQL script with PowerShell
Running a SQL script from PowerShell is not a difficult task. There basically are two options: use Invoke-Sqlcmd cmdlet or use sqlcmd.exe. Since I want to be able to run the SQL script directly from the VSO agent, I prefer to use sqlcmd.exe, which can be installed as part of the Microsoft SQL Server Feature Pack.
The script block needed to run sqlcmd is as follows: