How can I run SQL scripts which are there in Azure synapse analytics workspace using azure DevOps?

Question

I tried few steps:

1. Connected to azure cloud using service connection ( based on service principal), SPN has contributor level access at scope subscription and rg
2. Authenticated to Azure synapse analytics workspace using Get-AzSynapseWorkspace -Name $workspaceName
3. Get list of sql scripts using $scripts = Get-AzSynapseSqlScript -workspaceName $workspaceName
4. Now I am unable to find the command which can run the sql file in $scripts.

Note: Built-in SQL pool is there i.e. server less

I tried below command:

az synapse sql-script invoke --resource-group $resourceGroupName --workspace-name $workspaceName --name $scriptName --output table

I am unable to find correct command for my use-case

Answer 1

You can use PowerShell scripts to run scripts that are in serverless pools, invoke-sqlcmd with PowerShell. Use the command below to run the script:

$scriptfilepath = <scriptfilePath>
$Server = "<serverName>"
$database = "<dbName>"
$username = "<userName>"
$password = "<password>"
Invoke-Sqlcmd -InputFile "$(scriptfilepath)" -ServerInstance $(server) -Database $(database) -Username "$(username)" -Password "$(password)" -QueryTimeout 36000 -Verbose

For more information, you can refer to the links below:

• https://learn.microsoft.com/en-us/answers/questions/1120910/run-sql-scripts-stored-in-synapse-dev-instance-on

• sql server - how to execute sql script using azure devops pipeline - Stack Overflow