Yaml to deploy SQL script using Azure DevOps

Question

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
    }

    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

This step runs cmd.exe instead of PowerShell:

steps:
- script:

Check this link: steps.script definition

The script step runs a script using cmd.exe on Windows and Bash on other platforms.

Change it to PowerShell@2.