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 = “1.0.0.5”

$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)