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

Copper Contributor

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.

 

RajuD999_0-1715973662371.png

I have the below yaml code which is throwing errors.

Code:

 

 

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

Thanks in advance...

1 Reply

@RajuD999 

 

What is the return error?