SQL Server Error during dacpac deployment via Octopus

Question

We are using Octopus Deploy to deploy a SQL dacpac project from Visual Studio. We are seeing a very unusual error that we've never encountered before. There is VERY little info available regarding this error when we search the interwebs and we've looked into the few items that we have found, but are coming up empty.

Breakdown.... As Octopus picks up the nuget pkg containing the dacpac to be deployed, we are receiving this error message from octopus:

"An error occurred during deployment plan generation> Deployment cannot continue. Error SQL72018: External data source could not be imported but one or more of these objects exist in your source."

So, what it looks like is that the deployment fails when it is doing the comparison between what we are about to deploy and what is already on the target database. We've scripted out the database, looking over settings, etc. and see nothing odd about it. Nothing jumps out as to why the deploy might fail this step. The limited info out there about this error suggests that database triggers might be to blame. However, this database contains no triggers. Even more odd.... we scripted out the database, then used that script to create a new database on that very same server (with a different name) and we can deploy to it just fine, so the issue doesn't appear to be specific to the server.

Just looking for ideas from someone as to where we might go from here. As I mentioned, we've never encountered this before even through hundreds of dacpac deployments with Octopus.

Answer 1

probably not an answer but too long for comment

Since you could not reproduce the problem after creating brand new DB with the same contents my suggestions are:

1. Compare SQL Server version and Compatibility level on both sides - in sqlproj and target DB

2. Ensure your deployment process runs sqlpackage.exe at specific location (not a random version found in one of PATH locations) and this location matches the sql server version supporting given Compatibility level e.g. if you are on 150 then you probably should not be calling sqlpackage from ...\Microsoft SQL Server\130\DAC\Bin\..., it should be folder \150\ or \160\

3. Check for sqlpackage updates. There've been published several releases with bugfixes during last year.

4. Try excluding object types you never had an don't expect to appear in your projects from publish process via publish.xml or direct arguments passed to sqlpackage.

Anyways, specific version of sqlpackage and compatibility level matter in such errors, it'd be better if you published them in the question.