Custom Action to Run SQL Script With Parameters in VS 2013 Release Management Deployment Agent

How to pass parameters to SQL script running from VS 2013 Release Management? Let me show you step by step.

First we need a SQL Script to Run as a test. Here is a very simple SQL Script written in SQLCMD mode.
INSERT INTO [dbo].[Customer]
           ([Id]
           ,[Name])
     VALUES
           ($(Id)
           ,'$(Name)')
GO
This script is inserting a record to a table with two columns. The script cannot be directly executed in SQL Management Studio since the $(Id) and $(Name) variables are not set. If tried will get below error.
 
To test the script run the below command in command prompt.
sqlcmd -S "POC-DOLPHINQA" -d "TestDB" -i "C:\Temp\TestSQL.sql" -v Id=3 Name="Chandrasekara" –b

This adds a row to the table successfully.

To create a new Action in the VS 2013 Release Management –> Log on to Client and go to Inventory tab and select Actions –> Click New
Create an action as shown below.
 
Arguments should be
-S "__ServerName__" -d "__DatabaseName__" -i "__ScriptName__" -v __Params__ -b

This will add below parameters to the action

ServerName – SQL Server Instance Name
DatabaseName – Database Name the script should run
ScriptName – Script to execute with full path (In deployment machine. Copying script to deployment machine can be done using XCOPY Deployer)
Params – Parameters for the SQL Script

Now this action can be used in Release template.

Let’s try a release.

The new action execution succeeded.

Table is added with new record.

This custom action can be used even to execute script while dynamically changing target DB, tables, columns etc. using SQLCMD syntax.
For example
Use $(MyDatabaseName)
SELECT x.$(ColumnName)
FROM Person.Person x