Deploying Dacpac with Octopus

Here’s a full deployment script for deploying dacpac with Octopus to SQL 2014

# Set params

$databaseName = “AdventureWorksDW”

# Add the DLL

# For 32-bit machines

#Add-Type -path “C:\Program Files\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.dll”

# For 64-bit machines

Add-Type -path “C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.dll”

# Create the connection strnig

$d = New-Object Microsoft.SqlServer.Dac.DacServices “server=(local)”

#Set Package version such as $PackageVersion = “”

$PackageVersion = $OctopusParameters[‘Octopus.Action[Push nuget package to the server].Package.NuGetPackageVersion’]

#Set Application Directory, such as “C:\Octopus\Applications\Test\AdventureWorksDW\”

$ApplicationPath = $OctopusParameters[‘Octopus.Tentacle.Agent.ApplicationDirectoryPath’]

#Set Environment Name

$EnvName = $OctopusParameters[‘Octopus.Environment.Name’]

#Set PackageName

$PackageName = $databaseName

$dacpac = $ApplicationPath + “\” + $EnvName + “\” + $PackageName + “\” + $PackageVersion + “\” + $databaseName + “.dacpac”

Write-Host $dacpac

# Load dacpac from file & deploy to database

$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac)

# Set the DacDeployOptions

$options = New-Object Microsoft.SqlServer.Dac.DacDeployOptions -Property @{

‘BlockOnPossibleDataLoss’ = $true;

‘DropObjectsNotInSource’ = $false;

‘ScriptDatabaseOptions’ = $true;

‘IgnorePermissions’ = $true;

‘IgnoreRoleMembership’ = $true


# Generate the deplopyment script

$deployScriptName = $databaseName + “.sql”

$deployScript = $d.GenerateDeployScript($dp, $databaseName, $options)

# Return the script to the log

Write-Host $deployScript

# Write the script out to a file

$deployScript | Out-File $deployScriptName

# Deploy the dacpac

$d.Deploy($dp, $databaseName, $true, $options)

One thought on “Deploying Dacpac with Octopus

  1. You may get an error running this script with default settings of Powershell environment.
    See for a solution, my favourite is the one suggested by comment from Jorbes on April 12, 2013: create an environmental variable ‘COMPLUS_ApplicationMigrationRuntimeActivationConfigPath’ set to an arbitrary path containing the two config files powershell.exe.config and powershell_ise.exe.config. Both files should contain the following:

Leave a Reply

Your email address will not be published. Required fields are marked *