Share file between release stages in Azure DevOps (VSTS)

Question

Big Picture: We are using Azure DevOps release process (so far we create steps in designer, not YAML pipelines). We release to 3 environments with 3 different databases. Part of the release is deploy database from DACPAC file. Since using SqlPackage.exe to publish directly to database is not very transparent (you don't see and review the actual SQL script), we wanted to do release in 2 stages:

1. Create SQL script from DACPAC and review it
2. After approval run app and db deploy from previously generated script.

Issue: How to share sql script file between stages and how to see it for approval. Stages can be triggered on different agent.

What I've tried:

1. Publishing script as build artifact - this won't work cause to generate script I need to connect to database and connecting to it should not be part of build process, especially connecting to production database.
2. Publish artifact to Azure Pipelines as release step - it's not allowed on release, only for builds Publish
3. Publish artifact to file share - I'm not sure how this exactly work, documentation is not very well done. Moreover regular windows file sharing would be difficult to set in our infrastructure, I would rather avoid it.

Any other suggestions?

Answer 1

While you can't use pipeline artefacts, you could use Universal Packages in Package Management to publish arbitrary files for later retrieval. Just dumping the file contents to the pipeline logs is the easiest way to allow people to inspect it.

You could also create a file with placeholders as a build artefact and merge in the final settings from Pipeline variables in each stage, that way you can keep them as a build artefact. That's what I tend to do for any files of this nature. Sounds like this won't apply for your generated SQL file.

Alternatively, if the "seeing for approval" piece is important you could generate it and write it to the log, upload it to Universal Package Management. Then ask for approval at the end of the stage. In the next stage, you then download the script from Universal Package Management or you regenerate it using the exact same task configuration before execution.