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]
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.
FROM Person.Person x
SQL Server Integration Services (SSIS) projects can be created to perform ETL (Extract Transform and Load) operations. As Implementing of Co...
Generally windows services are deployed by creating an msi installer. It is possible to deploy msi via VSTS/TFS release management using the...
It is great if the latest updates to TFS can be applied as and when they are released. But for a large organization it might not be sometime...
Task groups are really useful to share common actions with multiple build or release pipelines in Azure DevOps (VSTS). You can group multip...
If you have upgraded the TFS from TFS 2015 to TFS 2018 you will encounter a situation where your vNext build agents 1.xx are no longer valid...