MSBuild and sqlcmd variables in data-tier applications PostDeployment.sql

Question

Preface

I use data-tier application project and SQL CLR Database project to manage Database part of my application.
I have 3 different machines (for local, dev/ci/qa and preprod/prod deployment environments accordingly) where Database part should be installed.
In PostDeployment script I create CLR stored procedures (with assembly) and logins and users.
Logins and users should be different depending on configuration.

Problem

I have problem using variables. Here are PostDeployment scripts I tried

GO
IF '$(DeploymentConfiguration)' = 'Debug'
BEGIN
    :r .\AddLoginsUsersRolesDev.sql
END
ELSE
BEGIN
    :r .\AddLoginsUsersRolesProd.sql
END
GO
....
ALTER DATABASE QProduction SET TRUSTWORTHY ON
GO
CREATE ASSEMBLY QProcedures from '[$(MSBuildProjectDir)]\Q.Core.Database.dll' WITH PERMISSION_SET = SAFE
GO
....

And

GO
:setvar databasename "$(TargetDatabase)"
:setvar deploymentconfig "$(DeploymentConfiguration)"
:setvar msbuildprojectdir "$(MSBuildProjectDirectory)"

IF '$(deploymentconfig)' = 'Debug'
BEGIN
    :r .\AddLoginsUsersRolesDev.sql
END
ELSE
BEGIN
    :r .\AddLoginsUsersRolesProd.sql
END
GO
....
ALTER DATABASE QProduction SET TRUSTWORTHY ON
GO
CREATE ASSEMBLY QProcedures from '[$(msbuildprojectdir)]\Q.Core.Database.dll' WITH PERMISSION_SET = SAFE
GO
....

Here is how I add logins, users (AddLoginsUsersRolesDev.sql):

GO
IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = 'Q\IUSR_Q')
BEGIN
    CREATE LOGIN [Q\IUSR_Q] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
END

USE [$(databasename)]
GO
IF NOT EXISTS
    (SELECT name
     FROM sys.database_principals
     WHERE name = 'Q\IUSR_Q')
BEGIN
    CREATE USER [Q\IUSR_Q] FOR LOGIN [Q\IUSR_Q]
END
GO

USE [$(databasename)]
GO
EXEC sp_addrolemember N'db_datareader', N'Q\IUSR_Q'
GO

USE [$(databasename)]
GO
EXEC sp_addrolemember N'db_datawriter', N'Q\IUSR_Q'
....

For PostDeployment.sql Action is PostDeploy and SQLCMD mode is on, for include scripts Action is Not in Build.
Deployment failed with "SQL Execution error: A fatal error occurred. The variable MSBuildProjectDirectory could not be found".
As far as I know in Database project I could use Database.sqlcmdvars for this purposes. How should I solve this problem?

Update

I've added

<SqlCommandVariablesFile>Database.sqlcmdvars</SqlCommandVariablesFile>

to .dbproj file and created Database.sqlcmdvars:

<SqlCommandVariables xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars">
  <Version>1.0</Version>
  <Properties>
    <Property>
      <PropertyName>databasename</PropertyName>
      <PropertyValue>Q</PropertyValue>
    </Property>
  </Properties>
</SqlCommandVariables>

This doesn't work.

I've added

<ItemGroup>
  <SqlCommandVariableOverride Include="databasename=$(TargetDatabase)" />
</ItemGroup>  

This doesn't work too.

I've added

<PropertyGroup>
 <databasename>Q</databasename>
 <SetVariables>
  <Variable Name="databasename" Value="Q" />
 </SetVariables>
</PropertyGroup>  

This doesn't work too (somewhere I've read that possible MSBuild variable should exists with the same name as sqlcmd one).

I've moved importing SqlTasks.targets project before PostBuildEvent section.
Before:

<PropertyGroup>
 <PostBuildEvent/>
</PropertyGroup>
<Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" /> 

After:

<Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" />
<PropertyGroup>
 <PostBuildEvent/>
</PropertyGroup>  

This doesn't work too.

Thank's.

Answer 1

Looks like the sqlcmdvars are not supported. At least they are marked "Not Applicable" for dacpac.