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

Raj D 581 Reputation points
2024-05-17T21:06:08.8566667+00:00

Greetings!!!

I'm trying to execute .sql files stored in azure git repo directory SQLScript with sub-directories for Tables, Views, Functions, StoredProcedures. Loop through each sub-directory and execute all the .sql files on the external SQL Server. We only have access to execute the SQL Server database object scripts and cannot do a .dacpac database deploy.

User's image

YAML:

variables:
  sqlServerConnection: $(System.ConnectionStrings.DatabaseConnectionString)
  sqlScriptPath: $(Build.SourcesDirectory)/SQLScript
steps:
- script: |
    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 
Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,977 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ManoharLakkoju 615 Reputation points Microsoft Vendor
    2024-05-20T03:41:21.49+00:00

    @Raj D
    Welcome to Microsoft Q&A Platform, thanks for posting your query here. Azure DevOps related queries/issues are currently not supported on this Microsoft Q&A platform.

    I would request you to please post your queries in dedicated forums as in below links:

    https://developercommunity.visualstudio.com/spaces/21/index.html

    https://developercommunity.visualstudio.com/t/get-unique-id-from-devops-organization/756710

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

    0 comments No comments