SQL Server 2014 Unit Test - Deployment to Build Server

Question

For the first time I am trying to use a Visual Studio 2013 SQL Server Database Project with a test project. It seems to work fine on my local machine but errors when run on the build server, using TFS2013.4, due to an incorrect path:

Database deployment failed. Path 'C:\Builds\1\Database1\Database1.sqlproj' is not a valid path to a database project file.

It cannot find the database project to deploy before running the tests.

This is a known feature, and there is an MSDN page on the subject here https://msdn.microsoft.com/en-us/library/jj851202(v=vs.103).aspx (see section under the heading "Modify the Test Project"). However, I don't find the explanation clear. There also seems to be some typo's e.g. instruction 6 seems to have you change the app.config from x to y and I cannot see a difference. They also seem to have an extra 't' in BuildComputer.sqlunitttest.config or is that intentional? Not sure.

Based on this MSDN page I now have an app.config as follows:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="SqlUnitTesting" type="Microsoft.Data.Tools.Schema.Sql.UnitTesting.Configuration.SqlUnitTestingSection, Microsoft.Data.Tools.Schema.Sql.UnitTesting, Version=12.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
  </configSections>
  <SqlUnitTesting  AllowConfigurationOverride="true">
    <DatabaseDeployment DatabaseProjectFileName="..\..\..\Database1\Database1.sqlproj" 
      Configuration="Debug" />
    <DataGeneration ClearDatabase="true" />
    <ExecutionContext Provider="System.Data.SqlClient" ConnectionString="Data Source=(localdb)\ProjectsV12;Initial Catalog=SimpleUnitTestDB;Integrated Security=True;Pooling=False"
      CommandTimeout="30" />
    <PrivilegedContext Provider="System.Data.SqlClient" ConnectionString="Data Source=(localdb)\ProjectsV12;Initial Catalog=SimpleUnitTestDB;Integrated Security=True;Pooling=False"
      CommandTimeout="30" />
  </SqlUnitTesting>
</configuration>

Note the attribute AllowConfigurationOverride="true" added to the SqlUnitTesting section.

And another config file called Azure-Dev-VS13.sqlunittest.config where Azure-Dev-VS13 is the build server name. The contents of this are:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
        <section name="SqlUnitTesting" type="Microsoft.Data.Tools.Schema.Sql.UnitTesting.Configuration.SqlUnitTestingSection, Microsoft.Data.Tools.Schema.Sql.UnitTesting, Version=12.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
    </configSections>
    <SqlUnitTesting>
        <DatabaseDeployment DatabaseProjectFileName="..\..\..\TestTeamProject\Database1\Database1.sqlproj" 
            Configuration="Debug" />
        <DataGeneration ClearDatabase="true" />
        <ExecutionContext Provider="System.Data.SqlClient" ConnectionString="Data Source=(localdb)\ProjectsV12;Initial Catalog=SimpleUnitTestDB;Integrated Security=True;Pooling=False"
            CommandTimeout="30" />
        <PrivilegedContext Provider="System.Data.SqlClient" ConnectionString="Data Source=(localdb)\ProjectsV12;Initial Catalog=SimpleUnitTestDB;Integrated Security=True;Pooling=False"
            CommandTimeout="30" />
    </SqlUnitTesting>
</configuration>

The only difference being the change to the path in the DatabaseProjectFileName attribute.

I have also added a Local.testsettings to the solution with a single entry under the Deployment category of

<solutionDirectory>\TestProject1\Azure-Dev-VS13.sqlunittest.config

However, no matter what I do I get the same error message, without the reference to the changed path, so I presume it is still reading the original app.config.

The examples above are from a test single machine setup where the development machine, TFS server and build server are one machine. However, I am getting the same errors on our full development environment.

Any ideas. Thanks.

Kevin Cunnane · Accepted Answer · 2015-07-29 03:01:16Z

1

A more detailed version of how-to guide is described in this SSDT blog.

Please use this blog post as a reference. MSDN documentation is being updated as we speak.

Three things are important

Requirement to update SqlDatabaseSetup.cs file in your test project. For C# following code needs to be added in SqlDatabaseSetup class.

static SqlDatabaseSetup() { Environment.SetEnvironmentVariable("VisualStudioVersion", "12.0"); }
TFS2013 has a different folder structure. The relative path should be set based on your setting. For instance, I have a solution 'E2ECI' with 'E2ECIDB' and 'E2ECITest' projects. It is under TFS project source control repository named 'DataDevelopers'. TFS build folder structure in my setting is e:\LocalTFSBuild\Builds\2\DataDevelopers\E2ECI\src\E2ECI\E2ECIDB.

The relative path starts from e:\LocalTFSBuild\Builds\2\DataDevelopers\E2ECI\bin directory so it becomes ..\src\E2ECI\E2ECIDB\E2ECI.sqlproj

Currently there is a known bug of overriding app.config file. Recommended way is make a modification to app.config file to set the path for TFS server and add additional config file for each developer or local dev machine in the solution for local test runs.

edited Jul 29, 2015 at 3:01

Kevin Cunnane

8,1501 gold badge35 silver badges35 bronze badges

answered Jul 28, 2015 at 21:34

Eric Kang

5013 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brendan Costigan Over a year ago

Your point three was I think most significant. I added a config file to the test project purely for use on the build server. In the build, I then run a pre-test batch file that replaces the normal config file with the build config file. Not ideal but it seems to work. Thanks.

Collectives™ on Stack Overflow

SQL Server 2014 Unit Test - Deployment to Build Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related