Octopus Deploy.ps1 DACPAC Deployment Fails

Question

I'm using Octopus on TeamCity to deploy a database to our production environment, but it keeps giving an error.

Here is the Deploy.ps1 code:

$dbName = $OctopusParameters['DBName']
$dbUser = $OctopusParameters['AdminDBUsername']
$dbPassword = $OctopusParameters['AdminDBPassword']
$dbSource = $OctopusParameters['DBDataSource']

# Set params
if (! $dbName)
{
    Write-Host "Missing required variable dbName" -ForegroundColor Yellow
    exit 1
}
if (! $dbUser)
{
    Write-Host "Missing required variable dbUser" -ForegroundColor Yellow
    exit 1
}
if (! $dbPassword)
{
    Write-Host "Missing required variable dbPassword" -ForegroundColor Yellow
    exit 1
}
if (! $dbSource)
{
    Write-Host "Missing required variable dbSource" -ForegroundColor Yellow
    exit 1
}

# Add the DLL
# For 64-bit machines
Add-Type -path ((Get-Item -Path ".\" -Verbose).FullName + "\bin\Microsoft.SqlServer.TransactSql.ScriptDom.dll")
Add-Type -path ((Get-Item -Path ".\" -Verbose).FullName + "\bin\Microsoft.SqlServer.Dac.dll")

# Create the connection string
$d = New-Object Microsoft.SqlServer.Dac.DacServices ("data source=" + $dbSource + ";User Id = " + $dbUser + ";pwd=" + $dbPassword)

#Load the dacpac
$dacpac = ((Get-Item -Path ".\" -Verbose).FullName + "\DeployScripts\Database.dacpac")
$dacpacoptions = ((Get-Item -Path ".\" -Verbose).FullName + "\DeployScripts\publish.xml")

#Load dacpac from file & deploy to database
$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac)

#Read a publish profile XML to get the deployment options
$dacProfile = [Microsoft.SqlServer.Dac.DacProfile]::Load($dacpacoptions)

# Deploy the dacpac
$d.Deploy($dp, $dbName, $TRUE, $dacProfile.DeployOptions)

Here is the publish profile in case it matters:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>MyDatabase</TargetDatabaseName>
    <DeployScriptFileName>MyDatabase.sql</DeployScriptFileName>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <BlockWhenDriftDetected>True</BlockWhenDriftDetected>
    <RegisterDataTierApplication>True</RegisterDataTierApplication>
  </PropertyGroup>
</Project>

Below is the error I'm getting:

Exception calling "Deploy" with "4" argument(s):

"An error occurred during deployment plan generation. Deployment cannot continue."

At Deploy.ps1:60 char:2

$d.Deploy($dp, $dbName, $TRUE, $dacProfile.DeployOptions)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException

FullyQualifiedErrorId : DacServicesException

Does anyone have any ideas what can be causing this? And how to fix it? Thanks in advance!

score 6 · Accepted Answer · answered Mar 06 '16 at 06:44

Ok guys I figured it out. Here's what I did:

in the ps1 file add a try catch block where $d.Deploy is called like so:

 try
 {
     # Deploy the dacpac
     $d.Deploy($dp, $dbName, $TRUE, $dacProfile.DeployOptions)
 }
 catch
 {
     Write-Host "LoadException";
     $Error | format-list -force
     Write-Host $Error[0].Exception.ParentContainsErrorRecordException;
 }

Doing this told me that the sqlproj file was aimed at Sql Server 2014, but the database being deployed to is Sql Server 2012. Simply go into the properties of the sql project file and change it so it's aimed at Sql Server 2012 and now it works! Huzzah!

Octopus Deploy.ps1 DACPAC Deployment Fails

1 Answers1