I have finally managed to implement SQL schema generation with database changes, followed by publishing those changes (after approval). Some remarks:
- This won't work if the changes will cause data loss.
- The path for the
sqlpackage is only correct when Visual Studio 2019 is installed, like in windows-2019 images.
- A previous
package.dacpac was previously generated by building the .sqlproj project(s).
- I passed the following variables through a group variable (more information on how to create group variables here):
targetDBConnectionStringservernamedatabasenameadminloginadminPassword
- I have added an approval to the
ApplyChanges stage
(within Pipelines menu, choose environments, then the ApplyChanges
environment, and then approvals and checks from the three dots
button, on the top right corner). This way the changes are not
applied to the database before manual approval takes place.
stage: VerifyScript
displayName: 'Script database schema changes'
dependsOn:
- Build
jobs:
- deployment: VerifyScript
pool:
vmImage: 'windows-2019'
variables:
- group: 'Timeline CV - Release'
environment: 'scriptverification'
strategy:
runOnce:
deploy:
steps:
- download: current
artifact: dropDacpac
patterns: '**/*'
- task: CmdLine@2
displayName: 'Generate schema changes script'
inputs:
script: |
"c:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\sqlpackage.exe" ^
/action:script ^
/diagnostics:true ^
/sourcefile:$(Pipeline.Workspace)\dropDacpac\path\to\the\dacpacFile\package.dacpac ^
/targetConnectionString:$(targetDBConnectionString) ^
/outputpath:$(Build.StagingDirectory)\changesScript.sql
- task: PublishPipelineArtifact@1
inputs:
targetPath: '$(Build.StagingDirectory)'
artifactName: dropSqlSchemaChangesScript
condition: succeededOrFailed()
- task: PowerShell@2
displayName: Show Auto Generated SQL Script
inputs:
targetType: 'inline'
script: |
Write-Host "Auto Generated SQL Update Script:"
Get-Content $(Build.StagingDirectory)\changesScript.sql | foreach {Write-Output $_}
- stage: ApplyChanges
displayName: 'Apply database schema changes'
dependsOn: VerifyScript
jobs:
- deployment: ApplyChanges
pool:
vmImage: 'windows-2019'
variables:
- group: 'Timeline CV - Release'
environment: 'applyChanges'
strategy:
runOnce:
deploy:
steps:
- download: current
artifact: dropSqlSchemaChangesScript
- task: SqlDacpacDeploymentOnMachineGroup@0
displayName: 'Deploy SQL schema changes script'
inputs:
taskType: 'sqlQuery'
sqlFile: '$(Pipeline.Workspace)\dropSqlSchemaChangesScript\changesScript.sql'
targetMethod: 'server'
authScheme: 'sqlServerAuthentication'
serverName: '$(servername)'
databaseName: '$(databasename)'
sqlUsername: '$(adminlogin)'
sqlPassword: '$(adminPassword)'
