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:

Comments

I am running into this exact same scenario. I wonder why they don't support typing in SqlCmd variables while using the SSMS Upgrade Data Tier Application wizard?

Oh well...like you said, I was leaning toward not relying on it anyway, for several reasons, and instead using SqlPackage.exe in general.

The other big reason, aside from automation, that I like the idea of SqlPackage.exe over the wizard, is for consistency in setting options such as 'block on drift detection' and 'drop objects in target but not in source', etc. In fact, I don't even see how you can set that stuff in the wizard.

I guess its just a case that the wizard isn't high enough up the priority tree. I've spoken to folks in Microsoft about it and they're aware of the issue(s) - I guess we just have to play a waiting game.

Like you I find sqlpackage.exe more feature rich than the wizard and that's why I prefer to use it. And because of automation, obviously.

Interesting you say that you use the drift detection features, you're the first person I've come across that uses those. I'd love to read/hear your thoughts about that.