MSDeploy/WebDeploy - Deploy Composite Database Project via DacPac

Question

We have a database project with views which join to tables in another database. The other database is in a different solution. This failed to build until we added a reference to a DacPac from the other database. I believe these views are considered “Composite Database Objects” because they reference objects not included as scripts in the database but rather referenced in the DacPac. We can successfully deploy this project from the VS to the target database.

Now we are trying to automate the deployment via MSDeploy. We have successfully deployed non-Composite database projects using DacPacs in the past by creating a package from a manifest. For example:

But in this case the deployment fails with the following error:

Error SQL0: The reference to external elements from the source named 'Other.dacpac' could not be resolved, because no such source is loaded. Warning SQL72025: No file was supplied for reference Other.dacpac; deployment might fail. When package was created, the original referenced file was located D:\BUILDS\6\CORE SERVICES\ACME DB (DEV2-DEPLOY)\SOURCES\ACME.SQLDEPLOY\DACPACS\Other.DACPAC.

Error: An error occurred while adding references. Deployment cannot continue. Error count: 1.

Is there a trick to make sure the referenced DacPac makes it into the package and the deployment completes successfully?

Pericardium

You could take this opportunity to re-work the application to avoid cross-database calls. Are they really needed? – Uncinariasis

Yes, they are needed. Some posts I've found indicate you can place the referenced DACPACs in the same folder as the DACPAC being deployed and it will find them. This doesn't seem to work when using MSDeploy/DbDacFx. – Pericardium

Answer 1

In the end we had to:

• Unzip the DacPac
• Change the path of the referenced DacPac to be relative
• Recalculate the checksum and update it
• Rezip the DacPac

Not ideal, but in the end we now have an automated deployment process for DacPac referencing DacPacs.

Pericardium

Thanks for the update. Worth noting that to recalculate the checksum the easiest way is to use CertUtil (comes with windows): CertUtil -hashfile .\model.xml SHA256 – Miscalculate

Answer 2

This is an old question, but we've found that if the DACPAC cannot be found on the path referenced, you can include it in the same directory as the referencing DACPAC.

e.g., if ABC.dacpac references the msdb database, the referencing location is expected to be at C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\120\SqlSchemas\msdb.dacpac

However, you can copy msdb.dacpac to the same directory as ABC.dacpac and Microsoft.SqlServer.Dac.DacServices will find it

Atomicity