How to use msbuild properties in sqlproj (SQL Server 2012) script

Question

I just updated my existing SQL Server 2008 r2.dbproj for SQL Server 2012.sqlproj (using SQL Server data tools).

I used to be able to define the SQLCMD variable in my project, and then determine the value by editing the project file to use the msbuild values, adding the following element:

<ItemGroup> <SqlCommandVariableOverride Include="ProjectDirectory=$(MSBuildProjectDirectory)" /> </ItemGroup> 

What I could use in my PostDeployment script as follows:

 SELECT * INTO dbo.MyTable FROM dbo.MyTable WHERE 1=2 BULK INSERT dbo.MyTable FROM '$(ProjectDirectory)\data\dbo.MyTable.dat' WITH (DATAFILETYPE = 'widenative') 

However, after the upgrade, this no longer works.

I tried adding the same entry to the new sqlproj, but the Publish function does not seem to pick it up and does not want me to give a value. If I put $(MSBuildProjectDirectory) , this is interpreted literally and fails.

In the new mode, what is the mechanism for specifying the local file path and / or using msbuild values?

Answer 1

In sql server 2012 sqlproj (SSDT database project) you are using profile publishing. You can start by right-clicking the database project and selecting Publish.

Then you can set the necessary parameters and save them in the so-called publication profile in your project. Double-clicking on this profile launches the publication wizard with the correct set of parameters.

In your published profile, you can include hardcoded values ​​for sqlcmd variables:

 <ItemGroup> <SqlCmdVariable Include="ProjectDirectory"> <Value>UNKNOWN</Value> </SqlCmdVariable> </ItemGroup> 

If you wish, you can update them using dynamic values ​​during assembly. In the msbuild project:

 <Target Name="SetProjectDirectoryInPublishXml"> <ItemGroup> <Namespaces Include="nsMsbuild"> <Prefix>nsMsbuild</Prefix> <Uri>http://schemas.microsoft.com/developer/msbuild/2003</Uri> </Namespaces> </ItemGroup> <ItemGroup> <SSDTPublishFiles Include="$(SolutionBinFolder)\**\*.publish.xml" /> </ItemGroup> <MSBuild.ExtensionPack.Xml.XmlFile Condition="%(SSDTPublishFiles.Identity) != ''" TaskAction="UpdateElement" File="%(SSDTPublishFiles.Identity)" Namespaces="@(Namespaces)" XPath="//nsMsbuild:SqlCmdVariable[@Include='ProjectDirectory']/nsMsbuild:Value" InnerText="$(MSBuildProjectDirectory)"/> </Target> 

This requires an extension to update XML. I am using the msbuild extension.

Credits for this arrangement go to Jamie Thomson