Passing Parameters to PS/DSC Scripts in vNext Release Templates – VS Release Management

In the vNext Release Templates there are few ways to pass parameters/configuration variables to a PS/DSC Script. Variables in five different levels can be used for this.
Global Variables – Defined at the Release Management Server level in Administration – > Settings – > Configuration Variables.

Server Variables – Defined at the Registered Target Server level in Configure Paths –> Servers –> Server –> Configuration Variables. The values defined in Server Variables will override the values of the variables defined in Global Variables.

Component Variables – Defined at the Release Component level in Configure Apps –> Components –> Component –> Configuration Variables. The values defined in Component Variables will override the values of the variables defined in Global and Server Variables.

Action Variables – Defined in the Release Template, Deploy Using PS/DSC action in Configure Apps –> vNext Release Templates –> vNext Release Template –> Deploy Using PS/DSC. The values defined in Action Variables will override the values of the variables defined in Global, Server and Component Variables.

All above four levels of Configuration Variables are supported with encrypted value by selecting the type Encrypted.

 
These variables can be managed in the release template using Configuration Variables and Resource Variables.



Variables in Configuration Script File  - These variables will be defined in a configuration script file and should be available with the build output. These files will allow to source control configuration variables for each stage. There is no encryption for the variables defined in a script file. The values defined in Script File Variables will override the values of the variables defined in Global, Server,  Component and Action Variables.


Passing Action Variables
To pass the Customer Name to the below script Action Variable can be used.

This script should be available in build output.

Component Should be setup to download the script to target server.

In the Deploy Using PS/DSC action the configuration variable CustomerName can be set like below.

When release triggered the scripts get downloaded to target server.

The value passed for CustomerName can be verified in the log.


Passing Component Variables
When component variable is only set it is getting passed to the script.



Action Variable Value Overriding Component Variable Value
When both Component and Action variable values set action variable value gets the precedence.



Setting Variables Using Script File
Configuration Variables can be set using a Script File as well.

This configuration variable script file should be available in build output.

When release triggered configuration variable script file is also copied to the target server.

Release PS/DSC script uses the configuration variable value defined in the configuration script file.


Script File Values Overriding Action and Component Variable Values
When value is defined for configuration variable in Component, Action and in a Configuration File the Configuration File get the precedence.

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