A Visual Studio solution file may contain both database project and a web application project.  When deploying (not building) the database project, it can generate a .sql file containing the full sql file or an incremental upgrade file against a database.  This blogs walkthrough one way to package database project generated sql file into the web application project’s package.  This solution works for team build scenario as well.

1. Create a Web Application Project

2. Add 2 new “SQL Server 2008 Database Project” to the solution

3. In each of the database project, add a sample table and make sure build and deploy are successful.  Check the deployed SQL directory %database project dir%sql%configuration% making sure sql file is generated

4. In Web application project’s property page, tab “Package/Publish SQL” , Add a new database entry for debug configuration.

5. Select it, and add the two database script files (generated in step 3) to the script list

6. Add extension pipeline project target file <projectName>.wpp.targets file in the same directory as the web project file.  How <projectName>.wpp.targets works is explained in how to package registry blog. For example, in the same directory as test1.vbproj file, add test1.wpp.targets file.  Make its content as following:

<!--********************************************************************-->
<!-- Task dependent db project custom deploy before build -->
<!--********************************************************************-->
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <AfterAddContentPathToSourceManifest Condition="'$(AfterAddContentPathToSourceManifest)'==''">
      $(AfterAddContentPathToSourceManifest);
      DeployDbProjBeforePackage;
    </AfterAddContentPathToSourceManifest>
  </PropertyGroup>

  <!-- Specifies each dependent database project here, its properties will be appended to the target msbuild Properties -->
  <ItemGroup>
    <MyDependentDBProjects Include="..Database1Database1.dbproj">
      <Properties>DeployScriptFilePath=..Database1sql$(Configuration)Database1.sql</Properties>
    </MyDependentDBProjects>

    <MyDependentDBProjects Include="..Database2Database2.dbproj">
      <Properties>DeployScriptFilePath=..Database2sql$(Configuration)Database2.sql</Properties>
    </MyDependentDBProjects>
  </ItemGroup>

  <Target Name="DeployDbProjBeforePackage">
    <!-- Make sure the corresponding directory exists before build the DB projects, especially useful for team build -->
    <Message Text="Creating directory: %(MyDependentDBProjects.rootdir)%(MyDependentDBProjects.directory)sql$(Configuration)" />
    <MakeDir Directories="%(MyDependentDBProjects.rootdir)%(MyDependentDBProjects.directory)sql$(Configuration)" />

    <MSBuild Projects="@(MyDependentDBProjects)" Targets="Build;Deploy" Properties="Configuration=$(Configuration);">
    </MSBuild>
  </Target>

</Project>

7. Save the project and make sure it builds.

8. Test the normal project package from command line, such as:  msbuild test1test1.vbproj /t:package

9. Test the team build solution package from command line, such as:  msbuild test1.sln /p:DeployOnBuild=true .  Note: DeployOnBuild parameter file is used exclusively for solution build, which asks web application project to package when build in the solution.

10. Check in to a TFS server and define a new build definition and test it in real team build environment

Note, the target file works for “Build Deployment Package” command in Visual Studio UI as well.  Since VS caches the targets file for a project, if you make editions to the targets file, in order for VS UI to use it, you might need to reload the project.

Xinyang Qiu | Visual Web Developer