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...
Task groups are really useful to share common actions with multiple build or release pipelines in Azure DevOps (VSTS). You can group multip...
You can easily clone a build and create a new build definition in the same team project. This is useful when you have similar type of applic...
Adding Azure Subscription to Azure DevOps as service connection is really simple when you have the same account you are using for Azure Dev...
Generally windows services are deployed by creating an msi installer. It is possible to deploy msi via VSTS/TFS release management using the...