A dacpac limitation – Deploy dacpac wizard does not understand SqlCmd variables

jamiet — Tue, 13 Nov 2012 21:53:04 GMT

Since the release of SQL Server 2012 I have become a big fan of using dacpacs for deploying SQL Server databases (for reasons that I will explain some other day) and I chose to use a dacpac to distribute my recently announced utility sp_ssiscatalog (read: Introducing sp_ssiscatalog (v1.0.0.0)). Unfortunately if you read that blog post you may have taken note of the following:

Ordinarily a dacpac can be deployed to a SQL Server from SSMS using the Deploy Dacpac wizard however in this case there is a limitation. Due to sp_ssiscatalog referring to objects in the SSIS Catalog (which it has to do of course) the dacpac contains a SqlCmd variable to store the name of the database that underpins the SSIS Catalog; unfortunately the Deploy Dacpac wizard in SSMS has a rather gaping limitation in that it cannot deploy dacpacs containing SqlCmd variables.

I think it is worth calling out this limitation separately in this blog post because its a limitation that all dacpac users need to be aware of. If you try and deploy the dacpac containing sp_ssiscatalog using the wizard in SSMS then this is what you will see:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Could not deploy package. (Microsoft.SqlServer.Dac)
------------------------------
ADDITIONAL INFORMATION:
Missing values for the following SqlCmd variables:SSISDB. (Microsoft.Data.Tools.Schema.Sql)
------------------------------
BUTTONS:
OK
------------------------------

The message is quite correct. The SSDT DB project that I used to build this dacpac *does* have a SqlCmd variable in it called SSISDB:

Quite simply, the Dac Deployment wizard in SSMS is not capable of deploying such dacpacs. Your only option for deploying such dacpacs is to use the command-line tool sqlpackage.exe.

Generally I use sqlpackage.exe anyway (which is why it has taken me months to encounter the aforementioned problem) and have found it preferable to using a GUI-based wizard. Your mileage may vary.

@Jamiet

Search results matching tags 'dacpac' and 'SQL Server 2012'

A dacpac limitation – Deploy dacpac wizard does not understand SqlCmd variables