Centric connect.engage.succeed

Custom Azure DevOps pipeline task to execute a SQL script

Geschreven door Hans Oude Middendorp - 20 april 2016

Hans Oude Middendorp
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>”.

Custom tasks

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:

$scriptBlock = {
    param($serverName, $databaseName, $useSQLAuthentication, $userName, $password)

    if ($useSQLAuthentication)
    {
        . sqlcmd -S $serverName -d $databaseName -U $userName -P $password
    }
    else
    {
        . sqlcmd -S $serverName -d $databaseName
    }
} 

Note that I have made it possible to choose between SQL authentication (with a SQL Server username and password) and Windows authentication (using the account that the VSO agent is running on).

Here is the complete parameter list for the PowerShell script:

  • runOnAgent: will sqlcmd.exe run on the agent or on a different server;
  • computerName: in case of running on a different server, the name of this server (exists in the VSO agent's network);
  • credentialUserName: username for logging on to this server;
  • credentialPassword: password for logging on to this server;
  • serverName: name of the database server (including SQL instance name);
  • databaseName: name of the database;
  • useSQLAuthentication: use SQL authentication (or Windows authentication);
  • userName: username of SQL account;
  • password: password of SQL account;
  • scriptName: full pathname of the SQL script.

The JSON file contains definitions for these input parameters: 

"inputs": [
    {
      "name": "runOnAgent",
      "type": "boolean",
      "label": "Run task script on agent",
      "defaultValue": "false",
      "required": true,
      "helpMarkDown": "Run this task on the VSO agent (needs Microsoft SQL Server CLI)."
    },
    {
      "name": "computerName",
      "type": "string",
      "label": "Remote computer",
      "defaultValue": "",
      "required": false,
      "helpMarkDown": "Remote computer on which SQL script must run.",
      "visibleRule":  "runOnAgent = false"
    },
    {
      "name": "credentialUserName",
      "type": "string",
      "label": "Username",
      "defaultValue": "",
      "required": false,
      "helpMarkDown": "Username of user on remote computer.",
      "visibleRule":  "runOnAgent = false"
    },
    {
      "name": "credentialPassword",
      "type": "string",
      "label": "Password",
      "defaultValue": "",
      "required": false,
      "helpMarkDown": "Password of user on remote computer.",
      "visibleRule":  "runOnAgent = false"
    },
    {
      "name": "serverName",
      "type": "string",
      "label": "Databaseserver name",
      "defaultValue": "",
      "required": true,
      "helpMarkDown": "Name (incl. instance) of SQL database server."
    },
    {
      "name": "databaseName",
      "type": "string",
      "label": "Database name",
      "defaultValue": "",
      "required": true,
      "helpMarkDown": "Name of SQL database."
    },
    {
      "name": "useSQLAuthentication",
      "type": "boolean",
      "label": "Use SQL Server Authentication",
      "defaultValue": "true",
      "required": true,
      "helpMarkDown": "Use SQL Server Authentication on database server (otherwise Windows Authentication is used)."
    },
    {
      "name": "userName",
      "type": "string",
      "label": "Database server username",
      "defaultValue": "",
      "required": false,
      "helpMarkDown": "User name for SQL logon.",
      "visibleRule":  "useSQLAuthentication = true"
    },
    {
      "name": "password",
      "type": "string",
      "label": "Database server password",
      "defaultValue": "",
      "required": false,
      "helpMarkDown": "Password for SQL logon.",
      "visibleRule":  "useSQLAuthentication = true"
    },
    {
      "name": "scriptName",
      "type": "filePath",
      "label": "SQL script file name",
      "defaultValue": "",
      "required": true,
      "helpMarkDown": "File name of SQL script that must be run."
    }
  ],

Note that “computerName”, “credentialUsername” and “credentialPassword” are visible (when defining the task in the Azure DevOps On-Prem or Azure DevOps web interface) when the value of “runOnAgent” is true. Note also that “userName” and “password” are only visible when SQL Authentication must be used.

The JSON file also contains information on how the task is executed:

"execution": {
    "PowerShell": {
      "target": "$(currentDirectory)\\RunSqlScript.ps1",
      "argumentFormat": "",
      "workingDirectory": "$(currentDirectory)"
    }
  }

The PowerShell script starts with the parameter definitions:

param (
    [string]$runOnAgent,
    [string]$computerName,
    [string]$credentialUserName,
    [string]$credentialPassword,
    [string]$serverName,
    [string]$databaseName,
    [string]$useSQLAuthentication,
    [string]$userName,
    [string]$password,
    [string]$scriptName
)

Note that the “runOnAgent” and “useSQLAuthentication” parameters are both string parameters and that these variables are defined as boolean in the “inputs” section of the JSON file. This is strange behaviour of the system that passes the variables to the PowerShell script; all parameters are passed as strings. The boolean parameters must be converted from string to boolean in the PowerShell script.

Use in vNext build definition or release definition

The task that was uploaded to Azure DevOps On-Prem or Azure DevOps can be used in a build definition or a release definition. You can select it when adding a task to the definition:

Add Tasks

The task definition will be shown as follows:

RunSqlScripts

The script file name is a file that is stored in the artifacts of the build or release definition and can be selected from the linked artifacts folder.

This is a great piece of tooling, isn’t it?

I have added my custom task definition in a zip file, so you can use it or to extend it to suit your needs.

About Hans
Craft Expert Hans Oude Middendorp  is part of the ALM team within Craft, the development programme for IT professionals (powered by Centric). If you would like to follow his blog, sign up for Craft updates.

Tags:ALM

     
Schrijf een reactie
  • Captcha image
  • Verzenden