Yaml to execute SQL scripts in a folder via Azure DevOps pipeline
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.
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…
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. 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… Read More