How to make SSDT Profile.xml SqlCmdVariable empty string or optional

Question

I use SSDT (and sqlproj) for our MSSQL projects. We have several variables that need to be set when publishing in each environment.

This works great in most of our environments where we assign values ​​to all variables, but when we publish our live database, I would like to make DomainPrefix an empty string.

When I try to modify the Live.profile.xml file to set DomainPrefix to a value, I get the error: "Error creating deployment plan. Deployment cannot continue. Missing values ​​for the following SqlCmd variables: DomainPrefix."

Here is what I would like Live.profile.xml to look like this:

<?xml version="1.0" encoding="UTF-8"?> <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="12.0"> <PropertyGroup> <TargetDatabaseName>DB_NAME</TargetDatabaseName> <DeployScriptFileName>DB_NAME.sql</DeployScriptFileName> <TargetConnectionString>CONNECTION_STRING</TargetConnectionString> <ProfileVersionNumber>1</ProfileVersionNumber> </PropertyGroup> <ItemGroup> <SqlCmdVariable Include="DomainPrefix"> <Value></Value> </SqlCmdVariable> <SqlCmdVariable Include="Environment"> <Value>live</Value> </SqlCmdVariable> </ItemGroup> </Project> 

Does anyone know how to set SqlCmdVariable to an empty value or make it an optional variable?

Using:

• VS 2013 sqlproj
• SqlPackage.exe to start publishing to the database from the command line

Answer 1

There seems to be no way to go through the NullOrWhiteSpace string as SqlCmdVariable . Basic syntax for SqlCmdVariable :

 <SqlCmdVariable Include="DomainPrefix"> <DefaultValue> </DefaultValue> <Value></Value> </SqlCmdVariable> 

Either Value or DefaultValue should have some non-white space value.

So, for your first option, as @Peter suggested in the commentary on the question, you can handle this in your Post Deployment SQL script by testing for a specific value, like <Live> or something else. Do something like the following:

 DECLARE @DomainPrefix NVARCHAR(50) = N'$(DomainPrefix)'; IF (@DomainPrefix = N'<Live>') BEGIN SET @DomainPrefix = ''; END; 

And then just @DomainPrefix with the strings instead of including $(DomainPrefix) in them.

Of course, if you need a SQLCMD variable that will be available in the main T-SQL context, and not just as a T-SQL variable (for example, if you use it as a bound server or a database prefix along the lines UPDATE $(DomainPrefix)[DatabaseName].[dbo].[TableName]... , where $(DomainPrefix) usually defined as [LinkedServerName]. ), you must leave using Value /**/ (or even /* Live */ ) so that the resulting T- SQL is interpreted as:

 UPDATE [LinkedServerName].[DatabaseName].[dbo].[TableName]... 

or

 UPDATE /* Live */[DatabaseName].[dbo].[TableName]... 

both of them are valid T-SQL and work. Therefore, in this case, you will use the following:

 <SqlCmdVariable Include="DomainPrefix"> <Value>/* Live */</Value> </SqlCmdVariable>