How to deploy DacPac from Octopus Cloud directly to Microsoft Azure SQL Managed Instance

Legacy Execution
1

Hey, I’m needing to deploy DacPacs to a handful of databases on a SQL Managed Instance from Octopus Cloud. The standard process seems to require some kind of jump box to download the package to before deploying. Is there any way around this? I’m not inclined to set up a VM whose sole raison d’être is to act as a proxy for something the Octopus Cloud Agent should be doing. Can someone tell me if this is the only way?

Cheers,

Ben

3

Hi Ben,

I’m assuming you are using the SQL - Deploy DACPAC community step?

If so, you can achieve what you want with a few changes to the script. Instead of getting the package from the previous step, you can add an additional referenced package so it all happens in one step.

  1. In the Octopus WebUI, go to Library -> Step Templates and select the SQL - Deploy DACPAC step.
  2. On the Parameter tab, remove the DACPACPackageStep parameter and add a new parameter named PackageId
  3. On the Step tab, add a Referenced Package, binding the Package ID to #{PackageId} and giving it a name Extracted
  4. Edit the script (Inline Source Code) and change the way Get-DacpacInstallPath works and remove references to DACPACPackageStep (also on lines 492 and 509). Get-DacpacInstallPath currently looks at the install directory of the previous step. You want to change it so that it looks at the extraction directory of the Referenced Package you added in step 3. The package (#{PackageId}) will be extracted to a subdirectory of the working directory named Extracted (the Name you give the referenced package).
  5. Go to your project and add this step, changing the Execution Location to Run on the Octopus Server and setting the PackageId parameter to the actual name of your package.

The DacPac script was contributed before we had additional packages, so if you get it working, it’d be great to contribute this alternate method to the library.

Another approach you can take, instead of the step template, is to take the script from the library step and embedding in the package. Then use the Run script step with the Run from package option to execute the script. The rest of the contents of the package will be extracted alongside the script to be executed. You will need to heavily modify the script to fit your environment and variables.

I hope this all makes sense.

4

Hey I worked through all this, but I’m getting the following error:

System.Management.Automation.RuntimeException: Could not find the file: Microsoft.SqlServer.TransactSql.ScriptDom.dll

It seems as though SSDT may not be available on Octopus Cloud directly? Am I missing something?

5

Hi Ben,

That is correct, we don’t install those tools. There are two ways to approach this:

  1. Run a step that downloads and installs the tools on the server. You’ll need to do this every time (or at least check whether you need to) as we regularly refresh the VM. You also don’t have administrative access to the VM.
  2. Bundle the required DLLs, add them as an additional package and load them into the powershell process (or put them into the DLL search path) using Add-Type or similar

I’ve successfully done option 2 with SQL SMO tools, and it should work the same for these unless they really need to be in the GAC. Others have bundled the tools on NuGet, but I can’t vouch for their authenticity and whether that violates the distribution licence for those assemblies.

6

If we don’t have administrative access to the VM, I’m assuming that a download and install of SSDT, even via Powershell, is a non-starter. I’m relatively sure it requires administrative privileges to install it… also,
deploying a package, which is the manner in which I would need to download the packaged DLLs, the deploy package step only functions on tentacles, so that makes that a non-starter too.

Are these assumptions correct or do you have a path to download them directly to the Octopus Server?

Cheers,

Ben

7

You can either include the DLLs as an additional package in your DacPac step, it will be extracted when the step executes. Or you an use the “Run a Script” step with a package and copy the DLLs somewhere.

8

Makes sense. Thank you so much for your help.

10

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.