THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

SQL Server Data Tools does support required variables

Over the past few years of using datadude (aka DBPro aka Visual Studio Database Projects) I have fallen prey to a peculiar little nuance – if you forget to supply a value for a sqlcmd variable then it will simply use a default and often that is not the desired behaviour. Hence why yesterday I submitted the following suggestion to :

Specify sqlcmdvars properties as "required to be overridden”

In my current place of work I am responsible for maintaining our datadude projects and we have another team that is in charge of deployments. Hence, when we place new properties into the sqlcmdvars file I need to tell the deployment team what values to supply for that property per environment (dev, systest, uat, prod).
Unfortunately lack of communication/human error occasionally creeps in and, for whatever reason, no value gets supplied for some property at deployment time. If this is the case the default value as specified in the sqlcmdvars file gets used instead - invariably this will be the wrong value. I would like a mechanism within SSDT of preventing this from ever happening.

One simple way to prevent this would be to specify that a sqlcmdvars property is *required* to be overridden during the deployment. In other words, never use the default. if an override is not supplied at deployment time then the deployment should fail.
Note that this stipulation should only be in place when deployment occurs from the command-line - if deploying from Visual Studio the default should be allowed (simply because Visual Studio doesn't provide a way to specify anything other than the default value supplied in the sqlcmdvars file).

It transpires that this requested feature is already available in the forthcoming SQL Server Data Tools (SSDT) as I shall now demonstrate. This screenshot shows the project properties of a SSDT project where we define the sqlcmd variables, I have defined a variable called $(Id_value):


In SSDT the nomenclature for deploying a project is “Publish”, a function that can be found by right-clicking on a project in Solution Explorer:


Selecting that brings up the Publish dialog.


Notice how the “Publish” button is greyed out – that it because I have not supplied a value for $(Id_value); supplying a value enables the Publish button and I can go ahead and publish my project. In other words, SSDT insists that I supply a value for that variable – exactly as I requested in my Connect submission.

The same is true if I use the command-line publishing tool sqlpackage.exe. The following command does essentially the same thing as the above depicted Publish dialog:

>sqlpackage.exe /TargetDatabaseName:MyDB /TargetServerName:".\RC0" /Action:Publish /SourceFile:"TestRequiredSqlCmdVars.dacpac"

Executing that gives me an error:

Publishing to database 'MyDB' on server '.\RC0'.
*** Missing values for the following SqlCmd variables:

Here’s a screenshot showing the same:


In order to supply a value for the variable from the command-line you need to use the /v: switch like so:

>sqlpackage.exe /TargetDatabaseName:MyDB /TargetServerName:".\RC0" /Action:Publish /SourceFile:"TestRequiredSqlCmdVars.dacpac" /v:Id_value=1


As you can see, publish was successful!

So there you go, using SSDT you’ll no longer be able to fall prey to the problem I highlighted at the top of this blog post.


Published Sunday, February 12, 2012 12:58 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



Peter Schott said:

Have you seen anything so far that you'd recommend SSDT over VS2010 or the other way around? I really like the programmer interface for SSDT, but if it doesn't support all of the major features from VS2010, I'm not sure that we'll be able to use it.  I know there was a survey recently for SSDT, but that brought up some possible concerns about feature parity when it comes to source control, versioning, refactoring, and such.

February 22, 2012 8:50 AM

jamiet said:

Hi Peter,

Yeah, just the feature explained above would be enough to make me switch to SSDT. Seriously.

I also like the fact that they are embracing Dacpac as the means for deployment. Notice the command-line I used above - I think that's a lot more friendly than what we have to do with VSDBCMD.exe today.

I know there is a lot of hate out there for Dacpac but from I've seen demo'd v3 has matured to become very useful (tho I haven't used it in anger yet).

I haven't heard about any major features from VS2010 that are not in there, and certainly haven't heard about anythnig in regard to source control, versioning or refactoring. (I know Schema View has disappeared but I think there'll be some good news about that on the way soon). What did you have in mind?



February 22, 2012 9:00 AM

Peter Schott said:

Thanks, Jamie.

Nothing in particular that I've heard - just the latest survey seemed to indicate that feature parity doesn't currently exist between SSDT and VS2010. Source control _should_ be there - it's just a bunch of files. That would imply branching exists. Refactoring is sometimes a necessary evil and a whole lot better than the alternative "drop, recreate, lose data" that otherwise exists.

I know that we've built in security handling based on your idea from a while back. Losing that would be painful as handling users and logins through the project is painful due to lack of multi-environment support.

I can't say too much about Dacpacs - we haven't used them too much. That may or may not be useful to us. My main concern there is the ability to easily customize what's released as our environments aren't completely in sync right now. :-(  I manually tailor release scripts for some of our environments to avoid creating/dropping objects that are needed.  (So can't wait for consistent environments and thus projects)

Last concern is handling cross database dependencies. Our product is built on 6 or so databases that have quite a few dependencies on each other, especially in the stored procs. We handle that now with DBSchema files so each database is relatively self-contained in each project. If SSDT can't support that, it's a deal breaker.

I know I can easily branch my projects and give it a try, but wasn't sure if SSDT peacefully co-exists with VS2010 or if I should use a VM for the testing.  I also realize you don't know everything about the product, but you're way further ahead than I am in testing and evaluation right now. Figured you might have an experience with something working way better (as above) or not at all (no specific examples).

Thanks for the quick response.

February 22, 2012 9:13 AM

SSIS Junkie said:

I have been using SQL Server Data Tools (SSDT) both at work and on some hobby projects for quite a few

May 8, 2012 6:07 PM

Peter Schott said:

I actually did find one area that's pretty big and missing from SSDT - Database Literal Variables. In VS2010 and prior you could specify your Database Reference to have a fixed value instead of using the [$(DBName)] syntax everywhere as a placeholder.  It felt a lot more natural.  Support for it is still there, but you have to manually edit the SQLPROJ file. That may be only if it's a brand new project, but I know that it's not the same. I'm working up a blog post on that and submitted a connect ticket as well - "SSDT Database References missing DatabaseVariableLiteralValue". It likely affects us more than others because we have 7 databases all talking to each other, but definitely a missed feature.

Other than that, I generally like what I see with SSDT. The deploy is much easier and I managed to get our entire set of DBs to deploy with very little trouble and a _huge_ boost in speed.

May 31, 2012 7:11 AM

jamiet said:


Interesting you say that its quicker. I hadn't noticed any discernible difference. Good news indeed.

May 31, 2012 7:15 AM

Peter Schott said:

I think it has to do with our 7 database scenario with most projects referencing at least 2-3 external schemas. Deploying a single project would analyze for probably 5-10 minutes and then run pretty quickly. That analysis time after the upgrade dropped down into the minute or two range which means that pushing a brand new set of databases now takes less than 10 minutes.  Before it was pretty much a "just go to lunch and it will be done when you return" scenario.

I don't know if this is because the dacpacs are compressed or not, but it's a noticeable speed difference in our scenario.

May 31, 2012 7:53 AM

Rich said:

Peter Schott, database literals are included. When creating the reference, just clear out the value in the Database Variable edit box.

June 22, 2012 5:36 PM

Peter Schott said:


 Thanks for mentioning that. I should have come back here to say the same. I posted in the MSDN group and got a response back from MS. Clearing out the "Variable" box does switch the reference over to a literal reference, it's just not very intuitive that this is the behavior. It's a lot easier than hacking the sqlproj file, too. :)

June 23, 2012 6:20 PM

piers7 said:

A plea for the follow up post :-) I'm struggling to work out how to deploy from the command line and just use the defaults (short of referencing an explicit profile, which contains it's own set of values, not necessarily the defaults). Last lines of the article imply you know...

August 1, 2013 1:51 AM

jamiet said:

Hi piers,

Hmmm... I must admit to being slightly confused. I've just tried it myself and you're right, there does seem to be no way of making it use the defaults when publishing from sqlpackage.exe. Seems the defaults are only there when using the debug experience (i.e. F5) within Visual Studio.

Apologies for the bum steer where I wrote above "In a later blog post I’ll show how you CAN supply a default value if you want to and also how you can override for your local environment." As soon as I post this comment I'll retract that.



August 1, 2013 4:50 AM

Chaitanya said:

Thanks it helped me

August 22, 2016 7:11 AM

Dorababu said:

Hello is there any possibility to achieve the same while extracting the dacpac, I have deployed the dacpac using cmd variable but when I am extracting and comparing I am seeing the difference as per the following image, how can I ignore this

May 2, 2017 2:34 AM

Leave a Comment


This Blog


Privacy Statement