Currently it looks like my best option is to pull the package from our artifact repository locally (step = “Run a script”, choosing to use “octopus server”), then use sqlpackage.exe to push to the Azure SQL database via the connection string (requiring SSDT installed locally on the OctopusDeploy server). Caveats include that my packages need to be variablized to be pulled down, which is somewhat frustrating. If there was a way to just integrate directly with my external connected feed from the command line, this task would be a bit less painless.
Are there any plans to implement a direct .nupkg deployment containing a dacpac file to Azure SQL database PaaS offerings in the future, straight from a connected external feed? Is there perhaps a better way to do this that I am totally missing / didn’t catch in the documentation?
The bad new first: we have no current plans to implement built-in support for this scenario.
We do have a step-template in our community-library for deploying DACPAC’s. Would this be of assistance to you?
I am not an expert in SSDT, so it’s highly possible I lack some understanding, but I’m not clear why you need to pull the package from your repository yourself. I believe the usual approach is to package the dacpac into a NuGet package and publish it to your chosen NuGet repository. Given you are deploying it to Azure, you then have two options:
Install a Tentacle on the Octopus Deploy server (or any machine you choose) and add a “Deploy a Package” step, referencing your package created above. You can then follow that step with your deployment PowerShell (or the step template from the community library) that uses the package installation location.
If you are using a version > 3.3 of Octopus Deploy, you can embed your deployment PowerShell inside your NuGet package along with the DacPac, and create a “Run a Script” step, choosing “Octopus Server” for the execution location, and “Script file inside a package” for the script source (see our Standalone Scripts documentation). This will cause the package to be extracted and your script to execute on the Octopus server. You can reference the contents of the package from your script using relative paths.
A little more further reading that may be of interest:
Thanks for the quick turnaround and for your support. It looks like the second option you mentioned executes what I have already achieved, albeit in a much cleaner fashion. By packaging my existing scripts in my .nuspec file I’ll be able to keep them in version control (instead of solely on the octopus server as a runstep) and ensure that they are always tied to the right package version (rather than pulling down the package from my script, increasing the potential room for error if I don’t update a variable pointing to the package URL / version).
That blog you posted was what spearheaded my script execution for deployment I’ve already written, so I know I must be on the right path. I have working deployments into Azure PaaS now, so I’ll refactor as you described and I should be off to the races.
This issue has been closed due to inactivity. If you encounter the same or a similar issue and require help, please open a new discussion (if we asked for logs or extra details in this thread, consider including them in the new thread). If you are the creator of this thread and believe it should not be closed let us know via our support email.