Headless MSBuild Support for SSDT (*.sqlproj) Projects

 

Update: breaking change: http://sqlproj.com/index.php/2012/10/dacfx-sept-2012-updates-break-headless-build/

This article describes how to install the required components to build and publish SQL Server Data Tools projects (*.sqlproj) using MSBuild without installing the full SQL Server Data Tool hosted inside the Visual Studio IDE.

In order to acquire the binaries needed you have to create an Administrative install of SSDT, which is described in detail in this previously published article.

NOTE: You could download all but one components from the SQL Server 2012 Feature Pack download page, however there currently is no separate download of the SSDTBuildUtilities.msi available.

Before we start, let’s setup a small test environment first. In this example I will start with a Windows Server 2008 installation, which has .NET 4.0 installed. I created a sample project on another machine using the IDE which I want to build and publish from my server. The sample project is available online: nw-sqlproj.zip, for this example I extracted the project in to the “c:\projects\nw-sqlproj” directory.

Second step is that we have to be able to call MSBuild.exe from the command prompt. In my example I am using a 32-bit Windows Server 2008 installation, after starting a command window using cmd.exe, I can invoke MSBuild.exe using:

%WINDIR%\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe

Normally I create a command file, named msbuild.cmd which I place in %WINDIR%, which looks like this:

@echo off

if (%PROCESSOR_ARCHITECTURE%)==(AMD64) (
    %WINDIR%\Microsoft.NET\Framework64\v4.0.30319\MSBuild.exe %*
) else (
    %WINDIR%\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe %*
)

Let’s start a command window and see what happens when we try to build our test project!

image

The result is clear, and expected at this point. The build fails, since the project system references the SSDT build task via the Microsoft.Data.Tools.Schema.SqlTasks.targets file.

Below the MSBuild output as text:

C:\Projects\nw-sqlproj>%WINDIR%\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe
Microsoft (R) Build Engine Version 4.0.30319.1
[Microsoft .NET Framework, Version 4.0.30319.239]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 3/7/2012 3:31:19 PM.
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" on node 1 (default targets).
ValidateSolutionConfiguration:
  Building solution configuration "Debug|Any CPU".
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (1) is building "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (2) on node 1 (default targets).
C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj(90,3): error MSB4019: The imported project "C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.
Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (default targets) -- FAILED.

Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (default targets)
 -- FAILED.

Build FAILED.

"C:\Projects\nw-sqlproj\nw-sqlproj.sln" (default target) (1) ->
"C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (default target) (2) ->
  C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj(90,3): error MSB4019: The imported project "C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.

    0 Warning(s)
    1 Error(s)

Time Elapsed 00:00:00.04

C:\Projects\nw-sqlproj>

In order to get the MSBuild task and dependent components installed, you need to perform the following five steps:

  1. Install the Microsoft® SQL Server® 2012 Data-Tier Application FrameworkX86 Package(dacframework.msi)X64 Package (dacframework.msi)
  2. Install the Microsoft® SQL Server® 2012 Transact-SQL ScriptDomX86 Package(SQLDOM.MSI)X64 Package (SQLDOM.MSI)
  3. Install the Microsoft® SQL Server® 2012 Transact-SQL Compiler ServiceX86 Package(SQLLS.MSI)X64 Package (SQLLS.MSI)
  4. Install the Microsoft® System CLR Types for Microsoft® SQL Server® 2012X86 Package(SQLSysClrTypes.msi)X64 Package (SQLSysClrTypes.msi)
  5. Install the SQL Server Data Tools Build Utilities from the Administrative install point.\ssdt\x86\SSDTBuildUtilities.msi

Now we are ready to try again!

image

And we can now successfully build our project.

Below the MSBuild output as text:

C:\Projects\nw-sqlproj>%WINDIR%\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe
Microsoft (R) Build Engine Version 4.0.30319.1
[Microsoft .NET Framework, Version 4.0.30319.239]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 3/7/2012 3:52:23 PM.
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" on node 1 (default targets).
ValidateSolutionConfiguration:
  Building solution configuration "Debug|Any CPU".
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (1) is building "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (2) on node 1 (default targets).
GenerateSqlTargetFrameworkMoniker:
Skipping target "GenerateSqlTargetFrameworkMoniker" because all output files are up-to-date with respect to the input files.
CoreCompile:
Skipping target "CoreCompile" because all output files are up-to-date with respect to the input files.
SqlBuild:
Skipping target "SqlBuild" because all output files are up-to-date with respect
 to the input files.
CopyFilesToOutputDirectory:
  nw-sqlproj -> C:\Projects\nw-sqlproj\bin\Debug\nw_sqlproj.dll
SqlPrepareForRun:
  nw-sqlproj -> C:\Projects\nw-sqlproj\bin\Debug\nw-sqlproj.dacpac
Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (default targ
ets).

Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (default targets)
.

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:00.40

C:\Projects\nw-sqlproj>

Now that we can build lets publish our project, using MSBuild.

C:\Projects\nw-sqlproj>msbuild /t:Publish /p:SqlPublishProfilePath=nw-sqlproj.publish.xml

This will publish the project to the server specified in the publish profile, which is an MSBuild structure XML file.

nw-sqlproj.publish.xml

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>NorthwindTest</TargetDatabaseName>
    <DeployScriptFileName>nw-sqlproj.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=localhost;Integrated Security=True;Pooling=False</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

MSBuild output of the Deploy operation:

C:\Projects\nw-sqlproj>msbuild /t:Publish /p:SqlPublishProfilePath=nw-sqlproj.pblish.xml
Microsoft (R) Build Engine Version 4.0.30319.1
[Microsoft .NET Framework, Version 4.0.30319.239]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 3/7/2012 5:37:52 PM.
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" on node 1 (Publish target(s)).
ValidateSolutionConfiguration:
  Building solution configuration "Debug|Any CPU".
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (1) is building "C:\Projects\nw -sqlproj\nw-sqlproj.sqlproj" (2) on node 1 (Publish target(s)).
SqlPublish:
  Deployment script generated to:
  C:\Projects\nw-sqlproj\bin\Debug\nw-sqlproj.publish.sql

  Creating NorthwindTest...
  Creating [dbo].[Categories]...
  Creating [dbo].[Categories].[CategoryName]...
  Creating [dbo].[CustomerCustomerDemo]...
  Creating [dbo].[CustomerDemographics]...
  Creating [dbo].[Customers]...
  Creating [dbo].[Customers].[City]...
  Creating [dbo].[Customers].[CompanyName]...
  Creating [dbo].[Customers].[PostalCode]...
  Creating [dbo].[Customers].[Region]...
  Creating [dbo].[Employees]...
  Creating [dbo].[Employees].[LastName]...
  Creating [dbo].[Employees].[PostalCode]...
  Creating [dbo].[EmployeeTerritories]...
  Creating [dbo].[Order Details]...
  Creating [dbo].[Order Details].[OrderID]...
  Creating [dbo].[Order Details].[OrdersOrder_Details]...
  Creating [dbo].[Order Details].[ProductID]...
  Creating [dbo].[Order Details].[ProductsOrder_Details]...
  ...
  ...<lines deleted for clarity>

  Update complete.
Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (Publish target(s)).

Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (Publish target(s)).

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:06.43

C:\Projects\nw-sqlproj>

And if we would publish it again, by default it will only perform an incremental update!

C:\Projects\nw-sqlproj>msbuild /t:Publish /p:SqlPublishProfilePath=nw-sqlproj.publish.xml
Microsoft (R) Build Engine Version 4.0.30319.1
[Microsoft .NET Framework, Version 4.0.30319.239]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 3/7/2012 5:41:23 PM.
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" on node 1 (Publish target(s)).
ValidateSolutionConfiguration:
  Building solution configuration "Debug|Any CPU".
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (1) is building "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (2) on node 1 (Publish target(s)).
SqlPublish:
  Deployment script generated to:
  C:\Projects\nw-sqlproj\bin\Debug\nw-sqlproj.publish.sql

  Update complete.
Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (Publish target(s)).

Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (Publish target(s)).

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:11.50

C:\Projects\nw-sqlproj>

We are done, we enabled build and publishing from MSBuild, without installing the SQL Server Data Tools IDE inside the Visual Studio shell.

I hope this helps you getting started with SQL Server Data Tools (SSDT)

@DataDude

This entry was posted in MSBUILD, SETUP, SSDT and tagged , , . Bookmark the permalink.

3 Responses to Headless MSBuild Support for SSDT (*.sqlproj) Projects

  1. Alex says:

    Is there any way to do this on windows 2003?

    I am trying to build an .sqlproj on a windows 2003 server.

    I get this error:

    The type initializer for ‘Microsoft.Data.Tools.Schema.Common.Diagnostics.EtwProvider’ threw an exception. —> System.PlatformNotSupportedException: This functionality is only supported in Windows Vista and above.

  2. Thanks for the article, we managed to get publish working on our build boxes with that map to the pieces that are required (why can’t Microsoft just package that up?), but then we decided that not only do we not want to have to call /t:Publish (we want this to happen in the solution with all the other projects, which don’t have publish targets), but we also don’t want to hard-code everything in a publish.xml file for each of our many branches…

    Eventually we figured out how to parametrize the database name and get a /p:DBDeployOnBuild=True to work. I wrote it up on my blog (and couldn’t help adding this here in case someone else like me comes across your blog looking for a way out of this Release.publish.xml craziness).

  3. Pingback: SSIS Junkie : Redistribution of sqlpackage.exe [SSDT]

Leave a Reply