How to execute a cmd file in VSTS CD pipeline

Question

I created a cmd file that basically executes all sql scripts sequentially. I am using a windows private build agent in VSTS. How do I execute this .cmd file? Is there any other task that executes multiple sql scripts against an Azure SQL database? Any suggestions will help. Below is the contents of cmd file:

@Echo Off FOR /f %%i IN ('DIR *.Sql /B') do call :RunScript %%i GOTO :END

:RunScript Echo Executing Script: %1 sqlcmd -S <> -U <> -P <> -d PFUTest -i %1 -o log.txt Echo Completed Script: %1

:END

starian chen-MSFT · Accepted Answer · 2018-02-15T01:36:56.337

1

Regarding execute sql scripts against an Azure SQL database, you can use Azure SQL Database Deployment task (Select SQL Script file or Inline SQL Script in Type input box of Deployment Package section.

With batch script, you can call Invoke-Sqlcmd command to run SQL scripts.

You may need to add Firewall rule during build/release, refer to this thread: Allow VSTS to update test database

edited Feb 15 '18 at 01:36

answered Feb 14 '18 at 03:12

starian chen-MSFT

33,174
2
29
53

Thanks..But it does not let me execute multiple scripts sequentially – vignesh srinivasan Feb 14 '18 at 03:33
What do you mean execute multiple scripts sequentially? – starian chen-MSFT Feb 14 '18 at 03:36
we have sql scripts named as '001_XXX', '002_XXX', etc. and the requirement is to execute the sql scripts in order – vignesh srinivasan Feb 14 '18 at 03:37
How about add multiple Azure SQL Database Deployment tasks for each script files? You also can custom build/release task by calling [Invoke-Sqlcmd](https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps) command. – starian chen-MSFT Feb 14 '18 at 05:05
Thanks. I do not want to create multiple azure sql database deploy tasks..i was able to get the batch script running in CD pipeline.it executes the SQL scripts in order against Azure SQL database – vignesh srinivasan Feb 14 '18 at 14:26
@vigneshsrinivasan Ok, you can call Invoke-Sqlcmd command to run SQL scripts. You may need to add Firewall rule during build/release, check the update of my answer. – starian chen-MSFT Feb 15 '18 at 01:38
Sorry..was away for a couple of days..yes I was able to solve this issue..thanks very much! – vignesh srinivasan Feb 19 '18 at 14:19

score 0 · Answer 2 · answered Feb 13 '18 at 18:55

0

Use the Batch Script task.

However, in general you should use SQL Server Data Tools to manage your database deployments.

answered Feb 13 '18 at 18:55

Daniel Mann

57,011
13
100
120

How to execute a cmd file in VSTS CD pipeline

2 Answers2