In our git repo we have a folder called ExternalSQLScripts with sub-folders for Tables, Views, Functions, StoredProcedures etc. Loop through each sub-folder and execute all the .sql files on the external SQL Server. We only have access to just deploy the SQL Server database object scripts we cannot do a .dacpac database deploy.

While I try to run my pipeline I run into 2 issues:

1) (Get-Module was unexpected at this time. Cmd.exe exited with code '255'.
2) PublishBuildArtifacts task throws error and I had to exclude it from the pipeline.

In general what would be the best approach to deploy SQL Server database scripts.

YAML:

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

steps:
- script: |
    # Install SqlServer module
    if (-!Test-Path (Get-Module -ListAvailable SqlServer)) {
      Install-Module SqlServer -Scope CurrentUser -Force
    }

    # Loop through each SQL script file
    Get-ChildItem -Path $sqlScriptPath -Filter "*.sql" | ForEach-Object {
      $scriptPath = $_.FullName
      $scriptName = $_.BaseName

      # Use Invoke-Sqlcmd to execute the script
      Invoke-Sqlcmd -ServerInstance $sqlServerConnection -Database [System.DefaultWorkingDirectory] -InputFile $scriptPath
      Write-Host "Successfully executed script: $scriptName"
    }
- task: PublishBuildArtifacts@1
    inputs:
      pathToPublish: $(sqlScriptPath)
      artifactName: sql-scripts