How to refer to files relative to project root in deployment of a database project?

Question

I have a database project in Visual Studio 2010 where in the post-deploy script I want to import the binary data from two files into the database. To that end, I have code very much like the following in a file referenced from the post-deploy script file:

INSERT INTO [dbo].[MyTable] ([Column1], [Column2])
SELECT t1.BulkColumn, t2.BulkColumn
FROM 
OPENROWSET(BULK N'$(ProjectDirectory)\Scripts\Post-Deployment\TestData\t1.dat', SINGLE_BLOB) AS t1,
OPENROWSET(BULK N'$(ProjectDirectory)\Scripts\Post-Deployment\TestData\t2.dat', SINGLE_BLOB) AS t2;

This has worked before. I'm not sure at what point it stopped working, but now, all I seem to get is the error SQL01262: The variable 'ProjectDirectory' has not been defined (ironically, with an error location of the post-deploy script itself, row 0 column 0). I dug through the files under the project root and found it kinda-sorta defined in the project file itself, which has the following within the top-level <Project> node:

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

Changing $(ProjectDirectory) to $(MSBuildProjectDirectory) in the snippet above changed nothing except the variable name in the error message. The error occurs during the validation stage of deployment, not during deployment itself.

What is the correct way to, in the post-deploy script, reference files relative to the project's root directory, for including the contents of those files in the database deployment?

Tim Partridge · Answer 1 · 2012-10-17T19:26:37.387

I have a very similar situation. The post-deployment script includes another sql file using the sqlcmd

:r "..\..\Another.sql"

In Another.sql I reference a variable defined in Database.sqlcmdvars and VS was complaining that the variable was not defined. I manually edited the project file. The PropertyGroup element did not contain the child elements DeploymentConfigFile and SqlCommandVariablesFile so I copied them from another PropertyGroup element. I have no idea if this is the correct solution but now the variable can be referenced.

  <PropertyGroup Condition=" '$(Configuration)' == 'Populate' ">
    <OutputPath>.\sql\Populate\</OutputPath>
    <TargetDatabase>SomeDatabase</TargetDatabase>
    <TargetConnectionString>Data Source=localhost;Integrated Security=True;Pooling=False</TargetConnectionString>
    <DeploymentConfigFile>Properties\Database.sqldeployment</DeploymentConfigFile>
    <SqlCommandVariablesFile>Properties\Database.sqlcmdvars</SqlCommandVariablesFile>
  </PropertyGroup>

How to refer to files relative to project root in deployment of a database project?

1 Answers1