THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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

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:

Error deploying dacpac containing a SqlCmd variable when using SSMS

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:

SqlCmd variable in an SSDT project

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

Published Tuesday, November 13, 2012 10:53 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ryan White said:

Hi Jamie,

I keep seeing your name on posts and things I'm researching :-)

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.

Ok...anyway, appreciate all your posts and thanks again,

Ryan

ryan.white@feisystems.com

May 29, 2013 11:47 AM
 

jamiet said:

Hi Ryan,

Thanks for the comment.

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.

JT

May 29, 2013 3:44 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement