After successfully setting up a deployment pipeline from VSTS to deploy our DACPAC to the target SQL server, we presented it to our Database Support team. Though supportive of the concept, they would like to have the ability to see the generated SQL scripts before publishing, which makes total sense.
If we were running SqlPackage.exe manually, this would be similar to executing SqlPackage.exe /Action:Script to see what will be executed.
In order to accomplish this, I added one more “Stage” to the VSTS Release Definition. In this stage, I basically run SqlPackage.exe /Action:Script and save the output *.sql file to a network location for someone to take a look at first (I wish there were a way to just output the resulted *.sql content to the output window).
An addition “Script” stage before the actual “Publish” stageSqlPackage.exe /Action:Script
After the output *.sql file is created, I added an Agentless phase and included a “Manual Intervention” task in it. In this task, I added the network location in its Instructions and set it up to notify the DB Support team. That way they will receive an email notification when the SQL script is ready for them to review.
Once they review the resulted SQL script, they can “Resume” or “Reject.” If resumed, the pipeline will continue onto the actual Publishing stage where the DACPAC will actually deployed. Win-win for everyone!
Manual Intervention task with notifications
