Using TeamCity to Build and Deploy SSDT Projects

In another post I talked about how we were asked to move away from TFS to start using GitHub, TeamCity and Octopus Deploy.  In this post I’m going to describe how to install TeamCity and how to build and deploy an SSDT project using it.  I’m running this on Windows 7.

Installation

  •  Firstly, download the latest version of TeamCity from the JetBrains website.
  • In the installation wizard select the install directory, install both the Build Agent and Server  components and choose the data directory.  You should be able to just use the default values for this.
  • Toward the end of the installation you’ll be asked to select a port to run the server component.  By default this will be 80 but if you already have an application using port 80 you will be told so.  Simply select a port not in use, for example 4567.
  • Next configure the Build Agent Properties.  You should be able to use the defaults for this.
  • Finally choose the accounts to run the server and agent services under.

Running TeamCity

  • Provided the TeamCity Server windows service is running you’ll now be able to browse to localhost:portNumber and see the server interface.  This is where you configure your builds.
  • The first time you use this interface you have to set up a few bits.  For testing purposes you can just use the “Internal (HSQLDB)” database type.
  • Once the final components have been set up agree the licence and then create an admin account
  • We’re now ready to use TeamCity but first we need an SSDT project to build and deploy.

Creating a test SSDT project

If you’ve not already got an SSDT project that you want to build you can either import a existing database into a new project or just create a very simple test project.

I’ve just created a new SSDT project named “SimonTeamCity” and created a table named “Person” with a couple of columns as follows:

TeamCity1

There a few ways that we can deploy this SSDT project but firstly I’m just going to create a publish.xml file to deploy to my local default instance.

TeamCity2

Now the project files exist we just need to add them to some sort of source control.  I’m going to use git for this and will just simply “Git Init” my solution directory. Please note, TeamCity does support building from TFS repositories (and many others).

Building the test project with TeamCity

The documentation in the Online Reference section of the Jet Brains website is good and you should be able to use this to build your project, but this I how I did it.

Firstly we need to create a new TeamCity project and this is done by selecting the “Projects” link at the top of the TeamCity server interface and then selecting “Create project”

TeamCity3

Give the project an appropriate name and click “Create”.

TeamCity4

Now we need to create the source control root.  Click on “VCS Roots” and then “Create VCS root”.  Select the Type of VCS as Git (or whatever source control you are using), add a VCS root name.  For git set the Fetch URL to be the local file path to the solution directory (i.e. your local git repository) and set the Default branch correctly.  You can click on “Test connection” to make sure everything is working correctly.

TeamCity5

Now under the “General Settings” we can create a new “Build Configuration” giving it an appropriate name.

Choose the existing VCS root to connect to and then the Build step page should be displayed.

You might have been given a few auto-detected build steps and there are several way to build the SSDT projects.  However, I think the easiest one is the “Visual Studio (sln)” build runner.

Edit or create your build step by using the “Visual Studio (sln)” Runner type and give it a Step Name.

If not already populated you can use the icon to the far right of the Solution file path text box to chose the .sln file

Choose the appropriate version of Visual Studio and just so we can test the build leave the Targets and Configuration as Rebuild and Release respectively.  You may want to show the advanced options and set an appropriate Platform, for example “Any CPU”.

TeamCity7

Save the build step and then click the “Run” button. Provided your agent is configured correctly and the agent service is running the build should start.  If you are not taken to the build run just click on the “Projects” link and after a little while you should see the success status

TeamCity8

You can click on the Success (or error) link to give more details of what happened during the build including seeing the Build Log.

Publishing the test project with TeamCity

In another post I’m going to demonstrate how to use TeamCity to generate a nuget package containing the compiled DACPAC that can be consumed and deployed using Octopus Deploy.  But now I’m just going to show how we can make a few simple changes to the Build Step to have TeamCity publish the SSDT project.

All we need to do is set the Targets to Build (or Rebuild) and Publish and then pass in the name of the publish.xml file into the Command line parameters

TeamCity9

Now if we run the build we should get a success message and can see in the build log that the SimonTeamCity database was created and the Person table added.

TeamCity10

We can also see that the database and table now exists on the server specified in the publish.xml file

TeamCity11

Continuous Integration

Now we can publish our projects to a database we can also set up TeamCity to trigger a build and deploy every time we check something into source control.  We can then create some tests using something like tSQLt that can be run after a successful publish to ensure we’ve not broken anything.

To add a trigger we just need to create one using the “Triggers” link in the “Build Configuration Settings”.  Create a VCS Trigger to detect the check ins.

TeamCity12

Now if I add a column to the existing table in my SSDT project, add a new table and check these in to my local git repository we should see that a build is automatically triggered and my local database gets updated.

TeamCity13

 

TeamCity14

Advertisements

6 thoughts on “Using TeamCity to Build and Deploy SSDT Projects

  1. Pingback: Source control, build and deploy of SQL Server databases using GitHub, TeamCity and Octopus Deploy | Simon Learning SQL Server
  2. Pingback: Using Octopus Deploy to Publish SSDT Projects Built with TeamCity | Simon Learning SQL Server
  3. Good article, thanks. Did you have to install Visual Studio 2013 (and SSDT) on the build server to make this work? When I try to build I’m seeing:

    error MSB4019: The imported project “C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets” was not found.

    • Hello Greg. I think I remember seeing the same error. I think you just have to install SSDT on the build server and it should create the required files. You shouldn’t have to install the full version of Visual Studio. Let me know if that fixes the problem.

      • Simon, thanks so much for the quick reply. The problem is, you can’t install SSDT for SQL2014 without installing Visual Studio 2013: http://msdn.microsoft.com/en-us/data/hh297027

        At least that’s I’ve gathered from everything I’ve read. I’m not sure what the “right” approach is here. Any thoughts?
        Kind Regards,
        Greg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s