how to execute sql script using azure devops pipeline

2
votes

I have SQL script which I want to execute using azure DevOps pipeline

I found multiple tasks for SQL but can not find any required task where I can pass sql server , database name and login details. Is there any task available for this ?

If there is not any task available and only way to execute is powershell script any sample available script for this ?

2
sql-serverpowershellazure-devopsazure-devops-pipelines

2 Answers

2
votes

You can use PowerShell to execute sql scripts. Example:

Invoke-Sqlcmd -InputFile "$(scriptfilepath)" -ServerInstance $(sqlserver) -Database $(dbname) -Username "$(username)" -Password "$(pwd)" -QueryTimeout 36000 -Verbose

Add custom variables (scriptfilepath, sqlserver, ...) and set values to them.

  1. PowerShell task
  2. Define variables
  3. Invoke-Sqlcmd
2
votes

You can use Invoke-Sql command like this

$SQLServer = "TestServerOne"
$db3 = "TestDB3"
$qcd = "PRINT 'This is output'"
Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd -Username "User" -Password "Password" -Verbose

Make sure SqlServer module is installed. It works also with Powershell Core

You can also try to use Run SQL Server Scripts Task extension