Run SQL / SQLCMD scripts against your Azure or On-Premise database passing multiple SQLCMD variables

SQLCMD mode is a convenient way to parameterize your deployment SQL scripts. This extension provides two pipeline tasks:

• Run single SQL Script in SQLCMD mode
• Run all SQL Scripts in a folder in SQLCMD mode

SQLCMD variables can be passed as name/value pairs in multiline text format, or as a basic JSON file. We recommend the JSON format as the content is easier to validate in Visual Studio Code.

This extension can be used in a build or deployment pipeline to run pre- and post-deployment SQL scripts. We strongly recommend that you design and build your database model using SQL Server Data Tools (SSDT) as this automates the generation of change scripts. SSDT models can then be conveniently deployed using our other component Publish DACPAC using a DAC Publish Profile.

New in Version 1.1.8

Supports SQL Server Authentication for both Azure and on-premise SQL servers. Two new fields have allow you to specify the authentication user/password for the SQL Server login. For obvious reasons, please use a secured variable in the password field!

Run single SQL Script in SQLCMD mode

The screenshot below shows the configuration options for the task.

Run all SQL Scripts in a folder in SQLCMD mode

The screenshot below shows the configuration options for the task.

The SQL scripts are run in alphabetical order. Simply rename the files with numeric prefixes to control the running order. Note that only scripts with a .sql file-extension are executed. Other files in the directory will be ignored.

Setting Recursive to true will cause the Run all SQL Scripts in a folder in SQLCMD mode task to run all the SQLCMD scripts it finds in the main folder and all sub-folders in alphabetical order.

Example Pipeline

Below is a sample pipeline including the Run SQLCMD extensions.