VSTS Continuous Build for DWH / BI

Continuous X / SQL Server / SSAS / SSIS / SSRS / Testing / Visual Studio

VSTS Continuous Build for DWH / BI

In my earlier post "Automated Deployments using Visual Studio" I metioned that the method described was a workaround because I hadn't figured out how to do a continuous build in VSTS (Visual Studio Team Services) yet. With hindsight, that workaround was not really needed: VSTS build turns out to be só easy, that it's ridiculous to try anything else. If you, like me until yesterday, don't know where to start to set up continuous build within VSTS, you're lucky. Below is a step-by-step explanation to setup your automated build for VSTS.

I've got a Data Warehousing solution consisting of the full MS BI stack:

  • Several SSIS projects
  • Some SSRS projects
  • One SSAS (Tabular) project
  • Four Database projects (DWH, Staging, DM, Metadata)

All of these can be built by VSTS within a hosted environment, natively! This means that you don't have to configure a worker machine to perform the build on, but can do build-as-a-service. The first 240 build minutes (= 4 hours) per month are free - so little reason to not give it a try!

In order to set up a new build definition, login to your VSTS portal. Open the project you want to set up Continuous Build for, then move over to "Build & Release" in the menu on top:

Select "New" (the big blue button top-right) to add a new build definition:

A new window opens, asking you which build definition standard to use. Choose "Visual Studio", then click next

The wizard will ask you where the sources should come from. You can get sources from Github, Subversion, other remote Git locations, or any project hosted within VSTS. In my case, I've got my sources hosted in Team Foundation Version Control (TFVC).

Two things to notice here:

  1. Continuous Integration will trigger a build on every checkin. This comes with a cost, of course - accidental checkins will trigger new builds too.
  2. A "Hosted" agent means build-as-a-service: you don't have to configure a local build agent or own any hardware for the build. For most DWH / BI builds this will suffice. To check if it will suffice for your project, take a look at the MSDN page about the Hosted Pool.

After clicking "Create", a build definition opens with pre-filled steps. This is a generic solution for almost every Visual Studio build, but caveat emptor! The "Visual Studio Build" task uses behind the scenes MSBuild - which doesn't support all of the BI projects. So we remove every step except the last two steps[ref]Of course, we could also have started with an empty template and then added the necessary steps, but when doing this for the first time it's easier IMHO to have a pre-filled working job and then trim out what you don't need[/ref]:

We don't need those steps in order to test if our build succeeds

After having removed all except the first two steps, add a new build step by clicking "Add new build step..."

In the popup, choose "Command Line" from the "Utility" category. Click "Add", close the popup and drag the task to the top of the tasks list.

In order to use the build (which builds all BI project types out of the box), enter the following properties for the new command line task:

  • Tool: %programfiles(x86)%\Microsoft Visual Studio 14.0\Common7\IDE\
  • Arguments: "$(Build.SourcesDirectory)\Path\To\Your\Solution.sln" /rebuild $(BuildConfiguration)

In my particular case, this looks as follows:

After having configured the build job, click "Save" and enter the name for the Build Definition.

Congratulations, the build definition is set up, and will run at every repository update (aka checkin)! Now to verify that it's working properly, let's schedule our first build. Click "Queue new build..." to do exactly that.

Then, in the popup window, the defaults are perfect, so just click "OK":

After clicking "OK", the agent will be initialized, the sources will be downloaded to the agent, and the build will take place:

If the job succeeds, the grey bar on top will change in a green one:

What's next?

As you can see, setting up hosted continuous build is really easy in VSTS. Two interesting things to look at are limiting which files to download (if your repository has more data than only the solution needed to build) and creating workitems automatically when a build failed. These are very easy to find out & configure once you've set up the build job, so I won't show them here.

Of course, after having finished a successful build, you will want to perform your tests automatically. I have to explore that part yet, but as soon as I'll do that, I'll blog about it - promise!

One last thing you should watch out for is name mismatches in Build Configurations within the solution and the underlying projects. By default, the "Copy files" task looks into the folder bearing the name of the build configuration - the name we provided when building the solution. However, when using for example the "Development" build config on solution level, we can map that to the "debug" build configuration on a particular project (using the Configuration Manager within Visual Studio). In this case, the "copy files" task won't find the output of the build!


Comments (5)

  1. Yorick

    Keep in mind that build errors will not always result in a failed build in VSTS this way. I'm not sure yet what causes a build to be marked as failed but I already encountered occasions wherein the build by Visual Studio failed but the build in VSTS got marked successful anyway.

    Also, 2015 BI projects are not yet supported...

    1. I see that SSDT 2015 isn't available by default on the hosted build client, but haven't run into any issues by the lack of it, even with a solution started in a VS2015 shell, containing SSAS Tabular, SSIS, SSRS as well as SQL Server projects. Did you run into specific problems ?

      Even w/o VS2015, doesn't work like devenv.exe in all cases (see also MS states "For build-related tasks, it is now recommended that you use MSBuild instead of devenv." (

      The good news: it's pretty easy to get SSIS, SSAS as well as SQL Server builds working using MSBuild. I will post an update shortly about that :).

      1. This might be a clue why VS2015 - while not supported officially - won't run into problems for BI projects:
        Unable to find MSBuild: Version = 15.0. Looking for the latest version - succeeds

        Indeed the VS2015-version of MSBuild is not available on the worker machine, but it defaults to the earlier version, which is apparently sufficent to build the Database & BI projects - at least with the released versions of the SQL Server stack I'm using here.

  2. astrid

    How do I know where the correct location of the devenv file for me is? the location on my computer? i tried it, and it didn't work.
    i am using visual studio 2017.

    1. Hi, I don’t think you should use the devenv way any more. Better split up your solutions by product (SQL Server, SSIS, SSAS...), then use the native msbuild for SQL and SSAS,
      SSISBuild (or another SSIS build tool, preferably from NuGet) for SSIS.

Comments are closed.