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

Question

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.

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 

Answer 1

@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