[SSDT] Specify sqlcmdvars properties as "required to be overridden" - by Jamie Thomson

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 724366 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 2/10/2012 7:14:17 AM
Access Restriction Public


[This is a suggestion for SSDT so please make sure it is routed to the correct person/people. thanks.]
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.

Occasionally this can have pretty dire consequences. In the last couple of days we came across a situation where the required value did not get supplied and the upshot was that the wrong value was being used in a filter predicate in a view - this didn't produce an error but it DID result in the wrong data being inserted into a table - and that issue was not found until weeks later. As you can imagine this has caused a big big problem.

I would like a mechanism within SSDT of preventing this from ever happening.

Sign in to post a comment.
Posted by Microsoft on 3/29/2012 at 9:38 AM
Hey Jamie,

Adam Mahood here. Just wanted to close the loop on this CMD var/command line issue. As you mention in your follow up comment and blog post SqlPackage.exe does indeed require you to override every SQLCMD variable that is defined in your .dacpac file and throws an error message if you don't.

If there are any other SqlPackage comments/questions that you have, please feel free to let me know!

Adam Mahood
Program Manager
SQL Server Data Tools / DACFx
Posted by Jamie Thomson on 2/12/2012 at 4:00 AM
Feel free to ignore this, its already sorted in SSDT: http://sqlblog.com/blogs/jamie_thomson/archive/2012/02/12/sql-server-data-tools-does-support-required-variables.aspx

Good news! :)
Posted by SQLDiablo on 2/10/2012 at 7:42 AM
I completely agree with this, since I've been bitten in the rear by it a number of times. Different developers have a project setup on their machine, but don't always realizing that environment-specific values need to be entered into the variables. It would be nice to have a warning or error mechanism that would bring this to the user's attention before/during a deploy.