Building and packaging SQL Server Integration Services projects in TeamCity

At DrDoctor we’ve been using Octopus Deploy for about a year and half now, and in that time we have developed a very robust way of releasing the core of our system. That is, two IIS websites and a bunch of windows services. Release automation has vastly increased the frequency at which we release software into production and in turn the speed at which the business can operate.

The missing part in all of this are the integration projects, when DrDoctor begins working with a new hospital we do a bunch of work integrating their PAS system into our system. This takes the form of a couple of staging databases and a couple of SQL Server Integration Services (SSIS) projects.

Whenever we are discussing changes that need to be made we always screw up our faces at the thought of doing the release. We also have no idea which clients have which version, or when we last did a release.

In this and a few subsequent posts I’m going to outline the various steps that I took to automate our deployments, so we can get on doing the interesting work and let the computers handle the boring deployment work.

Building SSIS Projects in TeamCity

The first step required is to have TeamCity build our Integration Services (SSIS) projects in TeamCity. In reality all it’s really doing is taking all the .dtsx packages that are part of the project and turning them into an ispac file.

The ispac is basically a zip file that contains all the packages and some other metadata which is used when deploying into SQL Server SSISDB.

There are two methods that come up from a Google search on getting TeamCity building SSIS projects, the first involves using a community MSBuild extension and a bunch of other bits, you can read more about it here or here, you can find the MSBuild extension on CodePlex. I tried this method, but didn’t have immediate success.

The second option is to use Visual Studio (SQL Server Data Tools) devenv.exe to build your project, this was already installed on our build server as it is required when building database projects.

To do this in the TeamCity project’s build configuration I added a new build step, which was a Command Line runner:

In the custom script I put in the full path to devenv.exe, our SSIS projects are built in SSDT BI 2012 (Visual Studio 2012 shell) so I’m using that version of devenv.exe on the build server.

Packaging SSIS Projects for deployment

Now that TeamCity is building the project it time to setup TeamCity to package the ispac file into a NuGet package. As mentioned in the intro, we use Octopus Deploy as our deployment system, it in turn uses the NuGet package format as the container for application files (more details).

Setting up TeamCity to automate the creation of NuGet packages turns out to be really easy.

Creating a NuSpec file

The first step is to create a NuSpec file, this is the manifest file that TeamCity will use when building the NuGet package (it tells TeamCity which files to include in the package).

To set this up I added a new file called Local_ETL.nuspec into my project folder with the following content

<?xml version="1.0" encoding="utf-8"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
  <metadata>
    <id>SSIS.Local_ETL</id>
    <version>1.0.0</version>
    <authors>DrDoctor</authors>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>Sync client to local</description>
  </metadata>
  <files>
    <file src="bin\Development\DrDoctor Client ETL.ispac" target="DrDoctorClientETL.ispac" />
  </files>
</package>

This as you can specifies a NuGet package ID, a version and a bunch of files that should be included.

Add build step in TeamCity

The next and final step is to configureTeamCity to create the NuGet package using the NuSpec file.

In TeamCity go into the Build Steps of your project, and click Add build step. From the runner type select NuGet Pack and configure the step with the following:

Make sure that you have ticked “Publish created packages to build artifacts”. This will ensure that the packages are available through the built-in NuGet repository.

In the next post I’ll show how I setup my deployment project in Octopus Deploy to deploy the SSIS ispac to SQL Server SSISDB.

Stay tuned.

Advertisement

Incremental Improvements – Quick Update on our Build Process

I’ve written previously about various aspects of the DrDoctor automated build process (you can read a few posts here, here and here). In the last week we’ve made a few more improvements that are worth a mention. The kudos for these improvements goes to my colleagues Steve (blog) and Jev.

Auto Tagging Production Releases

We have a branch that all of our releases into production go out from. We call it develop, but really we treat it more like master. Last week Steve setup TeamCity to automatically tag our git repository (hosted in BitBucket) after a successful build with the associated build number, but only on our develop (master) branch.

This is really useful, because up until that point we had no easy way of working out exactly which version of our code was running on our production box. We could work it out, but it involved clicking lots of links in Octopus Deploy, TeamCity and then git.

Now it’s the simple case of just looking at the name of the folder Octopus Deploy puts code into, as this will match the build number in TeamCity and therefore the tag in git.

Code deployed into folders with build number

Tags in out git repo (only for builds on our develop branch)

TeamCity build history

HipChat Notifications on Deployment

We use HipChat as a team to communicate throughout the day, we also have TeamCity integrated with it to tell us about successful/failed builds. Now, thanks to Jev we get a notification in HipChat when a deployment has been successful/failed into either our Testing or Production environment.

This is done as the final step in our Octopus Deploy Process using a Powershell script from the Octopus Deploy library which using HipChat API broadcasts into one of our chat rooms.

We have lots of ideas in the pipeline to continue making the build and deploy process even better. Stay tuned.

Deploying Database Projects with Octopus Deploy

Are you interested in automating your deployment process? Leave a comment and I would be more than happy to help you along the path, you won’t look back!

At DrDoctor we are currently under going a massive initiative to automate our deployment process. We have made excellent progress over the last couple of months,  and as it stands we can confidently and effortlessly release new versions of the various software components of the system (IIS websites, Windows services).

My colleague Steve has written on the stack which we are using to automate, at the core of it is TeamCity and Octopus Deploy.

From the beginning of this initiative there has always been an elephant in the room, a task that no one has wanted to tackle nor known how to. You can probably guess from the title of this post that I’m taking about database deployments.

At the moment the database upgrade part of our process looks like this:

1. Build the SQL Server Database Project in Visual Studio
2. Find the output directory of the SQL project
3. Connect to the remote server
4. Copy the DacPac file to the remote server
5. Look up syntax for SqlPackage.exe
6. Generate update script (using SqlPackage.exe)
7. Look over script in management studio
8. Run script

As you can see there are a lot of steps and one needs to remember various things, like the syntax for SqlPackage (I’ve just about memorised it now).

We are using Octopus Deploy as our deployment system, it works by copying NuGet packages to remote servers and then uses PowerShell scripts to install and configure. We also use TeamCity as our build server.

The guys at octopus deploy have a handy NuGet package which you install into the projects you want Octopus to deploy, and during the Build stage in TeamCity it will then turn these projects into NuGet packages, ready to be deployed.

Unfortunately there is no way to install NuGet packages into SQL Server Database Project types (not that I could find anyway), there are two ways of turning your database project into a NuGet package on TeamCity.

The first is to manually edit the project file adding the targets for OctoPack, the other method is to use a NuSpec file and the NuGet Package build step in TeamCity. The following snippet needs to be pasted at the very bottom of the .sqlproj file right before the </Project> tag.

<Import Project="..\..\..\packages\OctoPack.3.0.31\tools\OctoPack.targets" Condition="Exists('..\..\..\packages\OctoPack.3.0.31\tools\OctoPack.targets')" />
<Target Name="EnsureOctoPackImported" BeforeTargets="BeforeBuild" Condition="'$(OctoPackImported)' == ''">
  <Error Condition="!Exists('..\..\..\packages\OctoPack.3.0.31\tools\OctoPack.targets') And ('$(RunOctoPack)' != '' And $(RunOctoPack))" Text="You are trying to build with OctoPack, but the NuGet targets file that OctoPack depends on is not available on this computer. This is probably because the OctoPack package has not been committed to source control, or NuGet Package Restore is not enabled. Please enable NuGet Package Restore to download them. For more information, see http://go.microsoft.com/fwlink/?LinkID=317567." HelpKeyword="BCLBUILD2001" />
  <Error Condition="Exists('..\..\..\packages\OctoPack.3.0.31\tools\OctoPack.targets') And ('$(RunOctoPack)' != '' And $(RunOctoPack))" Text="OctoPack cannot be run because NuGet packages were restored prior to the build running, and the targets file was unavailable when the build started. Please build the project again to include these packages in the build. You may also need to make sure that your build server does not delete packages prior to each build. For more information, see http://go.microsoft.com/fwlink/?LinkID=317568." HelpKeyword="BCLBUILD2002" />
</Target>

NB: You will need to make sure that the relative paths to OctoPack is correct.

The downside to this obviously is that when a new version comes out I’ll have to manually update this path to point to the new version.

Now TeamCity will turn this into a NuGet package, at this point I can automate steps 1-4.

To automate steps 5 and 6 requires writing a PowerShell script which will call SqlPackage.exe. The following script will have the the required parameters to filled in during deployment by Octopus Deploy and use them to execute SqlPackage.exe:

$dbName = $OctopusParameters['DbName']
$user = $OctopusParameters['DbUser']
$pwd = $OctopusParameters['DbPassword']
$server = $OctopusParameters['DbServerName']

Write-Host DbName: $dbName
Write-Host User: $user
Write-Host Server: $server

& 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe' /Action:Script /OutputPath:Upgrade.sql /SourceFile:National.dacpac /TargetServerName:$server /TargetDatabaseName:$dbName /p:IncludeCompositeObjects=true /TargetUser:$user /TargetPassword:$pwd

It used to take about 30 minutes on average to do all this and was a massive pain just to deploy a relatively minor change. Now it takes between 5 and 10 minutes, depending on the amount of changes being applied. We now have a very simple process to follow.

Octopus Deploy doing the deployment of the database project and generating the upgrade script

The upgrade.sql script generated by SqlPackage.exe

TeamCity – Branching Databases and Continuous Integration

This week there have been lots of build failures, thanks to our HipChat and TeamCity integration I’m notified constantly. As a “senior developer”, this matters to me. The problem comes down to multiple feature branches using a single CI database, which means that it isn’t always “up-to date”, hence the failing tests.

In this post I’m going to show you how I fixed this by setting up TeamCity to publish a database for each of our feature branches.

There are a two bits that need to be done to achieve this end goal:

1. TeamCity needs to publish the SQL Server Database Project into a separate database for each branch
2. All connection strings throughout the solution need to be updated to point to this new database

Publishing the SQL Database Project

To accomplish this we will be using the SqlPackage.exe command line utility (MSDN docs) which is shipped with SQL Server Data Tools (you will have probably already installed this on your build server to get it to build the sqlproj). This command line tool does the same thing as when you right click in Visual Studio on your project and select publish.

We can use SqlPackage.exe to publish a new database by using the following command:

sqlpackage.exe
/Action:Publish
/SourceFile:"c:\path\to\my.dacpac"
/TargetDatabaseName:targetdb
/TargetServerName:sqlserver1
/Profile:"mydatabase.ci.publish.xml"

The arguments are pretty self-explanatory, except for perhaps the last one. The profile as you will see in a minute allows you to define different parameters in an XML file which are then used during deployment.

Creating the Continuous Deployment Profile

For our CI environment deployments I always want the database to be dropped and recreated, this can be configured in the deployment profile.

In Visual Studio, right click on your database project and click Publish. This will bring up the Publish Database window.

Then click Advanced, this will bring up the Advanced Publish Settings. Tick “Always re-create database”. The options you can select are extensive and worth a look through.

Click OK to close this and go back to the Publish Settings window.

To create the Profile simply click the “Create Profile” button, this will add a new {projname}.publish.xml file to your project.

I’ve renamed mine to Database.CI.publish.xml

Setting up TeamCity

The next thing to do is to have TeamCity actually publish the database project. In TeamCity add a new Build step:

1. Runner type: Command Line
2. Step name: Publish database
3. Run: Executable with parameters
4. Command executable: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe
5. Command parameters:

/Action:Publish
/SourceFile:src/Database/bin/Output/MyDatabase.dacpac
/TargetDatabaseName:MyDatabase.%teamcity.build.branch%
/TargetServerName:[server]
/Profile:src/Database/Database.CI.publish.xml

Then click Save.

Updating Connection Strings

Once TeamCity has built the solution and published the latest version of the database I needed a way to update all the connection strings in the various app.config and web.config files.

To accomplish this I put together the following PowerShell script:

$connectionStringName = $args[0];
$newConnectionString = $args[1];

# get the directory of this script file
$currentDirectory = [IO.Path]::GetDirectoryName($MyInvocation.MyCommand.Path)

$fileList = gci $currentDirectory -Recurse -Filter *.config

foreach($file in $fileList)
{
    $appConfig = New-Object XML
    # load the config file as an xml object
    $appConfig.Load($file.FullName)

    foreach($connectionString in $appConfig.configuration.connectionStrings.add)
    {

        if ($connectionString.name -eq $connectionStringName)
        {
            'file: ' + $file.FullName
            # write the name to the console
            'name: ' + $connectionString.name
            # write the connection string to the console
            'connectionString: ' + $connectionString.connectionString
            # change the connection string
            $connectionString.connectionString = $newConnectionString
        }
    }
    # save the updated config file
    $appConfig.Save($file.FullName)
}

What this does is it will find all *.config files files recursively from the directory that the PowerShell script lives in, it will then load each config file as an XML file and find all the connection strings, any that have a name that matches the first argument will have the ConnectionString property updated to the value of the second argument.

To configure TeamCity add a new build step:

1. Runner type: Powershell
2. Step name: Update connection strings
3. Powershell run mode: Version 2.0; Bitness: x86
4. Script: File
5. Script file: ConnectionStringUpdate.ps1
6. Script execution mode: Execute .ps1 script with “-File” argument
7. Script arguments: “MyDatabaseConnString” “Datebase=MyDatabase.%teamcity.build.branch%;Server=…”

Back into the green

Now when code is pushed up to BitBucket TeamCity will run through the following steps:

1. Build the solution
2. Publish the database project(s)
3. Update connection strings
4. Run unit test / integration tests – currently one step

TeamCity is back to being happy

and more importantly, when the build completes HipChat notifies me that the build has been successful.

 

Microsoft Fakes and TeamCity (and XUnit)

This post is a note to my future self on how to configure a TeamCity build to run tests that use Microsoft Fakes. If you haven’t ever come across Microsoft Fakes then take a look at this post – Testing the untestable with Microsoft Fakes (http://msmvps.com/blogs/bsonnino/archive/2013/08/11/testing-the-untestable-with-microsoft-fakes.aspx) for a good introduction.

Setting up the Build Agent

You will need to install Visual Studio 2012 and make sure that Update 3 is applied, no additional installation is required as Microsoft Fakes comes bundled.

If your tests are written using XUnit, which in my case they were then the next step is very important, (if you are using MSTest then you can skip this step and go to Configuring the Build Step).

You will then need to logon to the build agent machine with the user that the build agent runs as (i.e. not you!). Once logged on, launch Visual Studio and install the XUnit test runner. The reason for this is that Visual Studio extensions are installed per user. This caught me out!

Next up, you will need to download the TeamCity vstest logger from GitHub (https://github.com/nickers/vstest.teamcity.logger), build this and follow the instructions from the project page as to where the built DLL needs to go.

At this point, everything on the build server is setup and it is time to configure the TeamCity project.

Configuring the Build Step

Go into the setup of your project and add a new Build step.

Runner type: Command line

Step name: <can be whatever you like>

Execute step: Only if all previous steps were successful

Working directory: Set to the bin\Release (or bin\Debug) folder for your solution, depending on which one you are building/testing, this will most likely be Release

Run: Custom Script

Custom Script: C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\CommonExtensions\Microsoft\TestWindow\vstest.console.exe myinsertingproject.tests.dll /logger:TeamCityLogger /UseVsixExtensions:true

Update 21/11/2013 –

Turns out you do need to set the working directory, also updated the custom script to include the full path to the vstest.console.exe (thanks to @kevindrouvin)

Summary and Resources

That should be all you need to do to get the tests running and being reported back into TeamCity. Depending on how your environment path is setup you might need to give the directory to the vstest.console.exe in the Custom Script setup.

Here are a few different resources that I used:

• VSTest.Console.exe Command-Line Options – http://msdn.microsoft.com/en-us/library/vstudio/jj155796.aspx
• VSTest logger for TeamCity – https://github.com/nickers/vstest.teamcity.logger
• Unit testing – Visual Studio 2012 Fakes in Team City – http://blog.degree.no/2012/09/unit-testing-visual-studio-2012-fakes-in-team-city/