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

Question

I am using SSDT (and sqlproj) for our MSSQL projects. We have a few variables we need to set when publishing to each environment.

This works great on most of our environments where we assign values to all of the variables, but when we publish to our live database, I would like to be able to make the DomainPrefix a blank string.

When I try to alter the Live.profile.xml to set DomainPrefix to no value I get the error: "An error occurred during deployment plan generation. Deployment cannot continue. Missing values for the following SqlCmd variables:DomainPrefix."

This what I would like the Live.profile.xml to look like:

<?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 a SqlCmdVariable to a blank value or make it an optional variable?

Using:

• VS 2013 sqlproj
• SqlPackage.exe to run the publish to the DB from command line

Answer 1

There does not appear to be any way of passing through a NullOrWhiteSpace string as a SqlCmdVariable. The basic syntax for SqlCmdVariable is:

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

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

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

DECLARE @DomainPrefix NVARCHAR(50) = N'$(DomainPrefix)';

IF (@DomainPrefix = N'<Live>')
BEGIN
  SET @DomainPrefix = '';
END;

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

Of course, if you need the SQLCMD variable to be available in the main T-SQL context and not just as a T-SQL variable (for example, if you are using this as a Linked Server or Database prefix along the lines of UPDATE $(DomainPrefix)[DatabaseName].[dbo].[TableName]... where $(DomainPrefix) is usually defined as [LinkedServerName]. ), then you should be able to get away with using a Value of /**/ (or even /* Live */ ) so that the resulting T-SQL would interpreted as either:

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

or:

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

both of which are valid T-SQL and work. So in this case you would use the following:

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