Yaml to execute SQL scripts in a folder via Azure DevOps pipeline

Question

Greetings!!!

We have a git repo directory ExternalSQLScripts with sub-directories for Tables, Views, Functions, StoredProcedures. Loop through each subdirectory and execute all the .sql files on the external SQL Server. We only have access to execute SQL Server database object scripts and on this SQL Server instance we cannot do a .dacpac deployment.

YAML:

variables:
  sqlServerConnection: $(System.ConnectionStrings.DatabaseConnectionString)
  sqlScriptPath: $(Build.SourcesDirectory)/SQLScript

steps:
- script: |
    # Install SqlServer module
    if (-!Test-Path (Get-Module -ListAvailable SqlServer)) {
      Install-Module SqlServer -Scope CurrentUser -Force
    }
    Get-ChildItem -Path $sqlScriptPath -Filter "*.sql" -Recurse | ForEach-Object {
    $scriptPath = $_.FullName
    $scriptName = $_.BaseName

    try {
        Invoke-Sqlcmd -ServerInstance $sqlServerConnection -Database [System.DefaultWorkingDirectory] -InputFile $scriptPath
        Write-Host "Successfully executed script: $scriptName"
    } catch {
        Write-Error "Error executing script: $scriptName - $($_.Exception.Message)"
    }
}
- task: PublishBuildArtifacts@1
    inputs:
      pathToPublish: $(sqlScriptPath)
      artifactName: sql-scripts

Answer 1

Hello Raj,

Thanks for your question.

I understand you want to deploy Azure SQL via Azure DevOps pipeline.

To do this, you will have can use either a Dacpac deploy or Azure Powershell@5 task.

You will also need a service connection with the prerequisite privileges on the SQL server and Database.

To execute the deployment, I will recommend taking a look at the section on Azure Powershell deployment in the documentation here.

https://learn.microsoft.com/en-us/azure/devops/pipelines/targets/azure-sqldb?view=azure-devops&tabs=yaml

Please let me know if you have further questions

You can mark it 'Accept Answer' if this helped

.

Answer 2

@Raj D - Thanks for the question and using MS Q&A platform.

Azure DevOps is currently not supported in the Microsoft Q&A platform; the supported products are listed over here https://docs.microsoft.com/en-us/answers/products (more to be added later on).

In order to assist best on your query, I would request you to post your query in SO => Azure Devops dedicated support. Additionally, adding the [Azure] tag on SO will increase visibility as it is a Microsoft Sponsored tag.

https://stackoverflow.com/questions/tagged/azure-devops

OR

Report any Azure Devops problems on Developer Community.

This will assist you with a faster reply to your query.