visual-studiomsbuildintegration-testingsqlproj

Build and publish sqlproj from code for integration testing

I have a (2015) solution with a database (SQL Server 2008) project (.sqlproj) and I want to write integration tests using this database project. I need to recreate the database for each test but I can't seem to get it to work using Microsoft.Build.

In my test assembly I referenced following:

<Reference Include="Microsoft.Build, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" />
<Reference Include="Microsoft.Build.Framework, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" />
<Reference Include="Microsoft.Build.Tasks.Core, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" />
<Reference Include="Microsoft.Build.Utilities.Core, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" />

My publish profile file for the tests:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>OnTime</TargetDatabaseName>
    <DeployScriptFileName>OnTime.Database.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=.\SQLX;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <CreateNewDatabase>True</CreateNewDatabase>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

And the code I use to do setup:

[OneTimeSetUp]
public void Setup()
{
    var connectionString = ConfigurationManager.ConnectionStrings["OnTime"].ConnectionString;

    //var directory = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName;

    ILogger logger = new DebugLogger();

    // Set up properties.
    //https://msdn.microsoft.com/en-us/library/microsoft.data.tools.schema.tasks.sql.sqlpublishtask(v=vs.103).aspx
    var projects = ProjectCollection.GlobalProjectCollection;
    projects.SetGlobalProperty("Configuration", "Debug");
    projects.SetGlobalProperty("CreateNewDatabase", "True");
    projects.SetGlobalProperty("VisualStudioVersion", "14.0");
    projects.SetGlobalProperty("SqlPublishProfilePath", @"C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\Publish\OnTime.Database.publish.SQLEXPRESS.xml");
    // Load and build project.
    var dbProject = ProjectCollection.GlobalProjectCollection.LoadProject(@"C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj");
    var result = dbProject.Build(new[] { "Build", "Publish" }, new[] { logger });
}

Output I recieve from DebugLogger: (output from Publish task only)

Build started.
Project "OnTime.Database.sqlproj" (Publish target(s)):
Building with tools version "14.0".
Target "CheckRequiredProperties" in file "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" from project "C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj" (entry point):
Done building target "CheckRequiredProperties" in project "OnTime.Database.sqlproj".
Target "_CheckForInvalidConfigurationAndPlatform" in file "C:\Program Files (x86)\MSBuild\14.0\bin\Microsoft.Common.CurrentVersion.targets" from project "C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj" (entry point):
Task "Error" skipped, due to false condition; ( '$(_InvalidConfigurationError)' == 'true' ) was evaluated as ( '' == 'true' ).
Task "Warning" skipped, due to false condition; ( '$(_InvalidConfigurationWarning)' == 'true' ) was evaluated as ( '' == 'true' ).
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.Build.Tasks.Core\v4.0_14.0.0.0__b03f5f7f11d50a3a\Microsoft.Build.Tasks.Core.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Using "Message" task from assembly "Microsoft.Build.Tasks.Core, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a".
Task "Message"
Configuration=Debug
Done executing task "Message".
Task "Message"
Platform=AnyCPU
Done executing task "Message".
Task "Error" skipped, due to false condition; ('$(OutDir)' != '' and !HasTrailingSlash('$(OutDir)')) was evaluated as ('bin\Debug\' != '' and !HasTrailingSlash('bin\Debug\')).
Task "Error" skipped, due to false condition; ('$(BaseIntermediateOutputPath)' != '' and !HasTrailingSlash('$(BaseIntermediateOutputPath)')) was evaluated as ('obj\' != '' and !HasTrailingSlash('obj\')).
Task "Error" skipped, due to false condition; ('$(IntermediateOutputPath)' != '' and !HasTrailingSlash('$(IntermediateOutputPath)')) was evaluated as ('obj\Debug\' != '' and !HasTrailingSlash('obj\Debug\')).
Done building target "_CheckForInvalidConfigurationAndPlatform" in project "OnTime.Database.sqlproj".
Target "BeforePublish" in file "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" from project "C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj" (target "Publish" depends on it):
Done building target "BeforePublish" in project "OnTime.Database.sqlproj".
Target "PrePublishEvent" skipped, due to false condition; ('$(PrePublishEvent)'!='' And Exists($(TargetDir))) was evaluated as (''!='' And Exists(C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\bin\Debug\)).
Target "_SetupSqlPublishInputs" in file "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" from project "C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj" (target "SqlPublish" depends on it):
Done building target "_SetupSqlPublishInputs" in project "OnTime.Database.sqlproj".
Target "_SetupSqlPublishOutputs" in file "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" from project "C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj" (target "SqlPublish" depends on it):
Done building target "_SetupSqlPublishOutputs" in project "OnTime.Database.sqlproj".
Target "SqlPublish" in file "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" from project "C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj" (target "Publish" depends on it):
Building target "SqlPublish" completely.
Input file "ForceScriptRecalculation.txt" does not exist.
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Schema.Tasks.Sql.dll'. Cannot find or open the PDB file.
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.Build.Utilities.v4.0\v4.0_4.0.0.0__b03f5f7f11d50a3a\Microsoft.Build.Utilities.v4.0.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.Build.Framework\v4.0_4.0.0.0__b03f5f7f11d50a3a\Microsoft.Build.Framework.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Using "SqlPublishTask" task from assembly "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Schema.Tasks.Sql.dll".
Task "SqlPublishTask"
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Schema.Utilities.Sql.dll'. Cannot find or open the PDB file.
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Schema.Sql.dll'. Cannot find or open the PDB file.
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Utilities.dll'. Cannot find or open the PDB file.
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.SqlServer.Dac.Extensions.dll'. Cannot find or open the PDB file.
The "SqlPublishTask" task could not be instantiated from the assembly "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Schema.Tasks.Sql.dll". Please verify the task assembly has been built using the same version of the Microsoft.Build.Framework assembly as the one installed on your computer and that your host application is not missing a binding redirect for Microsoft.Build.Framework. Unable to cast object of type 'Microsoft.Data.Tools.Schema.Tasks.Sql.SqlPublishTask' to type 'Microsoft.Build.Framework.ITask'.
The "SqlPublishTask" task has been declared or used incorrectly, or failed during construction. Check the spelling of the task name and the assembly name.
The thread 0x34b8 has exited with code 0 (0x0).
Done executing task "SqlPublishTask" -- FAILED.
Done building target "SqlPublish" in project "OnTime.Database.sqlproj" -- FAILED.
Done building project "OnTime.Database.sqlproj" -- FAILED.
Build FAILED.

Running using MSBuild from commandline works fine

msbuild /t:Build;Publish /p:SqlPublishProfilePath=Publish\OnTime.Database.publish.SQLEXPRESS.xml

sqlproj file contents:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">
  <Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <Name>OnTime.Database</Name>
    <SchemaVersion>2.0</SchemaVersion>
    <ProjectVersion>4.1</ProjectVersion>
    <ProjectGuid>{7ce126e0-b091-428f-8983-0c3db87922ad}</ProjectGuid>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql100DatabaseSchemaProvider</DSP>
    <OutputType>Database</OutputType>
    <RootPath>
    </RootPath>
    <RootNamespace>OnTime.Database</RootNamespace>
    <AssemblyName>OnTime.Database</AssemblyName>
    <ModelCollation>1033, CI</ModelCollation>
    <DefaultFileStructure>BySchemaAndSchemaType</DefaultFileStructure>
    <DeployToDatabase>True</DeployToDatabase>
    <TargetFrameworkVersion>v4.5.2</TargetFrameworkVersion>
    <TargetLanguage>CS</TargetLanguage>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <SqlServerVerification>False</SqlServerVerification>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseSet>True</TargetDatabaseSet>
    <DefaultSchema>OnTime</DefaultSchema>
    <GenerateCreateScript>True</GenerateCreateScript>
    <IsChangeTrackingOn>True</IsChangeTrackingOn>
    <ChangeTrackingRetentionPeriod>7</ChangeTrackingRetentionPeriod>
    <SccProjectName>SAK</SccProjectName>
    <SccProvider>SAK</SccProvider>
    <SccAuxPath>SAK</SccAuxPath>
    <SccLocalPath>SAK</SccLocalPath>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <OutputPath>bin\Release\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>False</TreatWarningsAsErrors>
    <DebugType>pdbonly</DebugType>
    <Optimize>true</Optimize>
    <DefineDebug>false</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <OutputPath>bin\Debug\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>false</TreatWarningsAsErrors>
    <DebugSymbols>true</DebugSymbols>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <DefineDebug>true</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup>
    <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">11.0</VisualStudioVersion>
    <!-- Default to the v11.0 targets path if the targets file for the current VS version is not found -->
    <SSDTExists Condition="Exists('$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets')">True</SSDTExists>
    <VisualStudioVersion Condition="'$(SSDTExists)' == ''">11.0</VisualStudioVersion>
  </PropertyGroup>
  <Import Condition="'$(SQLDBExtensionsRefPath)' != ''" Project="$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <Import Condition="'$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <ItemGroup>
    <Folder Include="Properties" />
    <Folder Include="Tables" />
    <Folder Include="StoredProcedures" />
    <Folder Include="Views" />
    <Folder Include="Publish" />
    <Folder Include="Scripts" />
  </ItemGroup>
  <ItemGroup>
    <Build Include="Schema.sql" />
    <Build Include="Tables\Contract.sql" />
    <Build Include="Tables\Customer.sql" />
    <Build Include="Tables\Log.sql" />
    <Build Include="Tables\User.sql" />
    <Build Include="StoredProcedures\Report.sql" />
    <Build Include="Views\LogDetailView.sql" />
    <Build Include="Login.sql" />
    <Build Include="User.sql" />
  </ItemGroup>
  <ItemGroup>
    <PostDeploy Include="Scripts\Seed.sql" />
  </ItemGroup>
  <ItemGroup>
    <None Include="Publish\OnTime.Database.publish.SQLEXPRESS.xml" />
  </ItemGroup>
</Project>

This is my main issue I think:

The "SqlPublishTask" task could not be instantiated from the assembly "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Schema.Tasks.Sql.dll". Please verify the task assembly has been built using the same version of the Microsoft.Build.Framework assembly as the one installed on your computer and that your host application is not missing a binding redirect for Microsoft.Build.Framework. Unable to cast object of type 'Microsoft.Data.Tools.Schema.Tasks.Sql.SqlPublishTask' to type 'Microsoft.Build.Framework.ITask'.

Solution

  • After looking a bit deeper in the output I received I started looking at the referenced Microsoft.Build assemblies. It appears I have multiple installed.

    Be sure to add the following:

    C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETFramework\v4.5.2\Microsoft.Build.dll

    C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETFramework\v4.5.2\Microsoft.Build.Framework.dll

    The others were installed in C:\Program Files (x86)\Reference Assemblies\Microsoft\MSBuild\v14.0 (version 14.0.25420.1)