Run SQL Script With Parameters in VS 2013 Release Management Deployment Agent - Step by Step

How to pass parameters to SQL script running from VS 2013 Release Management? Let's see step by step.

Below is a very simple SQL Script written in SQLCMD mode, to test the tool.

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.
http://lh5.ggpht.com/-tlvv1ubhj_g/U-ErBnTl9nI/AAAAAAAACxQ/-dzL64xGYsE/a2_thumb%25255B1%25255D.png?imgmax=800 
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
http://lh6.ggpht.com/-X50vk4ajt5s/U-ErDaS_7XI/AAAAAAAACxg/9A_zqftiAhw/a3_thumb%25255B3%25255D.png?imgmax=800
This adds a row to the table successfully.
http://lh3.ggpht.com/-DJLFgSvblBU/U-ErFF49tfI/AAAAAAAACxw/X8hfBlYQzQ4/a4_thumb%25255B2%25255D.png?imgmax=800
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.
 http://lh3.ggpht.com/-8j-6JrcuIGo/U-ErG-SmjiI/AAAAAAAACyA/XY6-ptQmAaM/a1_thumb%25255B7%25255D.png?imgmax=800
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.
http://lh3.ggpht.com/-J7D7i71hxi4/U-ErIgKLyCI/AAAAAAAACyQ/Gxi25QRNHLk/a2_thumb%25255B3%25255D.png?imgmax=800
Let’s try a release.
http://lh3.ggpht.com/-6GOSzp8OC-I/U-ErKYLY3VI/AAAAAAAACyg/s1x1FqUUAFI/a3_thumb%25255B6%25255D.png?imgmax=800

 The new action execution succeeded.
http://lh6.ggpht.com/-6Oan7lzn6tE/U-ErL6Qt5ZI/AAAAAAAACyw/f1tm-ofGuNI/a2_thumb%25255B5%25255D.png?imgmax=800

 Table is added with new record.
http://lh6.ggpht.com/-Aopv8JXoUoM/U-ErNjLOZhI/AAAAAAAACzA/9wbdeIxN6P4/a4_thumb%25255B5%25255D.png?imgmax=800
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