zaterdag 25 augustus 2018

DevOps : Investigating different options 'building' Data warehouse / BI project in VSTS (part V)


I'm gaining more and more knowledge and experience with the Microsoft VSTS environment and 'building' a typical data warehouse and BI project. Now, there is 'building' and there is 'building'. I mean with building, the 'Build' option as we know it in Visual Studio, resulting in deployable packages or others. In Visual Studio, you can build a solution or a project and this will result in a deploy able package, for instance a dacpac or an asdatabase file. 

A typical Data warehouse/BI project may consist of the following types of projects :
  • a Database project.
  • a SSIS project.
  • a SSRS project.
  • a SSAS Tabular project.
  • a SSAS Multidimensional project.

In order to setup a build and release process, the first step is to build these kind of projects and there seems some confusion on how to build these kind of projects with VSTS. These are the options:
  • Use MSBuild Task.
  • Use Visual Studio Build Task.
  • Use commandline Task.
  • Use a third part solution Task.
  • Use Custom Powershell script in a Powershell task.
For each of these types of projects, I'll explore and describe the different options of building the different type of projects.

Database project build

First, let's start with a database project. This is the main project of every Microsoft Data warehouse/BI project. In a database project there are all kinds of database objects stored and those can be build in a so called dacpac file. Such a dacpac file is deployable on different environments. There are a couple of options and I'll explore them and describe them here. There is a lot of information available on building such projects in VSTS. 

MSBuild build
This is the most popular option regarding building a database project. MSBuild is the Microsoft Build engine and is used for building software, not only database projects but also software like C#. Now below is a screenshot shown of a typical build with a MSBuild task in VSTS. 

So there are five steps building a database project:
  1. Get the code from the repository (I use Git).
  2. Choose the right agent (I use VS2017).
  3. Build the project with MSBuild.
  4. Copy the files from the build folder to the artifacts staging folder.
  5. Publish the files to the artifacts folder, where you can download the file.

I've included the specific build step code here. Just specifying the sqlproj file is enough to build the sql projects. The **/* is a wildcard for every subfolder were .sqlproj exists. build
Yet another option for building a database project is using is the IDE environment and is used as a container for creating applications with different languages. In the folowing build process in VSTS I've replaced the MSbuild task with a command line task and used as the command line utility to build the database project.

I've used the following settings for the commandline:

I specified the solution, whether or not to rebuild and the project location.

Visual Studio Build
Yet another task, that is available for building a database project is the Visual Studio Build task. The options almost covers the same options of the MSbuild and the command line utility

I've used the following settings :

And here I say Build every sqlproj file in a folder.

SSIS project

Also an important part of a data warehouse project is ETLing with SSIS and therefore it needs a deployment package for releasing it in a DevOps environment. This will be a ispac file.

MSBuild build
I tried to build a SSIS project with MSBuild but the MSBuild doesn't recognize the dtproj file, unfortunately. That is a pity. The following error occurred.

The element <DeploymentModel> beneath element <Project> is unrecognized.
It is possible building a SSIS Project with the tool. This is done exactly in the same way as building a database project with

Below I've included the specific steps for building a SSIS Project with the program

Tool :
C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\

/rebuild $(BuildConfigurationSSIS) 

Visual Studio Build
Now building the project with Visual Studio is also a problem. There is also an error returned when executing the Visual Studio Build.

The target "Build" does not exist in the project.

SSIS Build by Toxic globe
SSIS Build & Deploy is a 3rd party tool that is developed by ToxicGlobe and is available on the Marketplace. It is free. It has quite some downloads and a good rating. I've tested the build task and it works great.

There are a couple of more 3rd party custom tasks that build and/or deploy SSIS task. I haven't tried them myself.

Custom Powershell script
Now, In my builds for my customer, I'm using and for some reason, strange and unpredictable errors occurs with Builds that ran fine for weeks now suddenly run into errors. Yet even strange is that when I build the SSIS project I run into errors, but when someone else checks in the code and triggers a build it just run fine (!?). So, I started looking into something else. Now, I encountered an option I would like explore some more and that is a Powershell script that is proposed by Koos van Strien. Here, he rebuilds the ispac content files from scratch and zips it into an ispac file. A nice solution, but it has some drawbacks like, will this script run in future versions of ispac file? Nevertheless, I going to give it a try in my project and see if it comply with my needs.

$dtProjFolderToLoad = '$(Build.SourcesDirectory)\SSISWideWorldImporters-SSDT'
$dtProjFileToLoad = Join-Path $dtProjFolderToLoad 'SSISWideWorldImporters-SSDT.dtproj'
[xml]$dtProjXmlDoc = New-Object System.Xml.XmlDocument
$dtProjXmlDoc.PreserveWhitespace = $true
# Create folder with the project name. This will essentially be zipped into an ispac
$ispacFolder = '$(Build.SourcesDirectory)\SSISWideWorldImporters-SSDT\bin\$(BuildConfigurationSSIS)\SSISWideWorldImporters-SSDT'
New-Item -ItemType Directory -Force -Path $ispacFolder
# Create the project manifest in the ispac folder
# Exists in node /Project/DeploymentModelSpecificContent/Manifest/SSIS:Project
$projectManifestXml = $dtProjXmlDoc.Project.DeploymentModelSpecificContent.Manifest.Project.OuterXml
$projectManifestFullPath = Join-Path $ispacFolder "@Project.manifest"
$projectManifestXml | Out-File $projectManifestFullPath -NoNewline
# Add [Content types].xml, which has a static content
$contentTypesXml = "<?xml version=`"1.0`" encoding=`"utf-8`"?><Types xmlns=`"`"><Default Extension=`"dtsx`" ContentType=`"text/xml`" /><Default Extension=`"conmgr`" ContentType=`"text/xml`" /><Default Extension=`"params`" ContentType=`"text/xml`" /><Default Extension=`"manifest`" ContentType=`"text/xml`" /></Types>"
$contentTypesFullPath = Join-Path $ispacFolder '[Content_Types].xml'
$contentTypesXml | Out-File -LiteralPath $contentTypesFullPath -NoNewline
# Iterate over all SSIS packages (*.dtsx) inside the .dtproj file add them to the ispac folder
$dtProjXmlDoc.Project.DeploymentModelSpecificContent.Manifest.Project.Packages.Package | ForEach-Object { 
    $fileToCopy = (Join-Path $dtProjFolderToLoad ([string]$_.Name))
    Copy-Item $fileToCopy $ispacFolder 
# Iterate over all project-level connection managers (*.connmgr), add them to the ispac folder
$dtProjXmlDoc.Project.DeploymentModelSpecificContent.Manifest.Project.ConnectionManagers.ConnectionManager | ForEach-Object { 
    $fileToCopy = (Join-Path $dtProjFolderToLoad ([string]$_.Name))
    Copy-Item $fileToCopy $ispacFolder 
# Copy the parameters file to the ispac folder
$paramsFullPathSource = Join-Path $dtProjFolderToLoad "Project.params"
Copy-Item $paramsFullPathSource $ispacFolder
# Archive the ispac folder as a ".ispac" file
Compress-Archive ($ispacFolder + "\*") ($ispacFolder + ".zip") -Force
Rename-Item ($ispacFolder + ".zip") ($ispacFolder + ".ispac") -Force
Remove-Item $ispacFolder -Recurse -Force    

Again all the credits to Koos van Strien. It just works perfectly. Almost;-) I had to make a small adjustment in the powershell script because of an error :

Name cannot start with character ., hexadecimal  0x00. row 1, position 40. (System.Xml)

I had to replace a line in the script with an unicode specified

$contentTypesXml | Out-File  -Encoding "UTF8" -LiteralPath $contentTypesFullPath –NoNewline

Below a screenshot of the powershell script

A SSRS project

Another important project involved in a Microsoft DWH and BI project is a SSRS project, a reporting project. Building a report project results in a copy of the rdl files to the build folder.

MSBuild build
I tried to build a SSRS project with MSBuild but the MSBuild doesn't recognize the rptproj file, unfortunately. The following error occurred.

The target "Build" does not exist in the project.

Rptproj project files are also not supported by MSbuild.
Fortunately, with the command line utility it is possible to build your SSRS project. Once setup properly, it will run smoothly.

These are the settings, I used for building this project file

Tool :
C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\

/rebuild $(BuildConfigurationSSRS) 

Visual Studio Build
Building a SSRS project with the Visual Studio Task will give the following error:

The element <DeploymentModel> beneath element <Project> is unrecognized.

3rd party task
There is a third party task and it is downloaded 80 times (at the moment of writing) and so not heavenly used. I tried it shortly but didn't manage to make it work in VSTS. 

SSAS tabular project

It is possible to build a SSAS tabular project with MSBuild, just like the database project build. 

Building a Tabular project is executed with the MSBuild task and now the extension .smproj is used for building the projects.
I don't have a working example available for building a tabular project with

Visual studio build
This is an example of building a Tabular project with Visual studio build task

3rd party
I didn't try the 3rd party examples for building a tabular project.

SSAS Multidimensional project

Again, the build of SSAS Multidimensional project is not possible with MSBuild and therefore use devenv again to build the SSAS multidimensional project.

Tool :
C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\

/rebuild $(BuildConfigurationSSIS) 
/project "$(Build.SourcesDirectory)\SSASMDWideWorldImporters-SSDT\SSASMDWideWorldImporters-SSDT.DWproj"

A typical build

Now, we have gathered the findings of the experiments with the different options of building a BI project, we can now set up a best practice for setting up a Build in VSTS. I have created the following steps :
  • Build Projects.
  • Copy the builds to the artefactsfolder.
  • Publish the artefacts in the artefactsfolder.

Now this results in the following artefacts:

Some files are not needed and can be excluded in the copy task, for instance, or setting the build options. It is also possible to group the different type of project files in separate project artefacts. In that case, the setup of the build pipeline is bit different.

Final thoughts

There are several options for building data warehouse projects : MSbuild, Visual Studio Build, 3rd party builds and Powershell scripts. At first it can be very confusing what task to use for a what project. Here is an oversight of my experiments with the different tasks in VSTS.


zondag 15 juli 2018

DevOps : master.dacpac does not exist error message in VSTS (part V)

One of the problems I had with automated builds was the database reference in the .sqlproj file. I noticed that the master- and the msdb dacpac was referenced to a location on the C: drive. This works fine in the situation where you're the only developer, but when working together in a team and even more when you use VSTS as your build environment, it is not a good practice. If you try to build in VSTS when the dacpac has a standard location, you'll get a message like this:

C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\
does not exist.

To get rid of this error message you have to include the master.dacpac (and the msdb.dacpac) file (!) in your project (with location option, add database reference) and so I did...

But building the project in VSTS kept on giving this error. Investigating the .sqlproj file showed me that the reference was still there, even when I deleted the database reference in the solution explorer.

I finally manually removed the entries in the .sqlproj file and the problems were away...

<ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\
<ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\


vrijdag 13 juli 2018

DevOps : Building your SSIS project in VSTS (Part IV)


In previous blogposts, I've explained how to build a database project (Part I), how to release a database project (Part II) and how to use pre- and post deployment script (Part III) in an automated build and release process with VSTS. The next step is to examine how to build and release a SSIS project. This blogpost is the result of this exploration.

Building code in a database project is done with MSBuild. It is not possible to use MSBuild for building a SSIS project (as far as I know). The options I've have found so far are :
  • Using command line task with 
  • Download a custom SSIS task from the marketplace.
I'll examine both ways but will describe the first one in this blogpost. I've used the following sites as an inspiration :

Creating a SSIS project in a solution

Now the first step in this demo is creating a SSIS project in my WorldWideImporters (I think I mixed up the name;-) ) solution. The database project is still present. It is the same project as I described in my previous blogposts.

The next step is to build the SSIS project. In the following step the changes are committed and pushed to the Git repository.

The first step is checking whether the build and release is still working and it is. But now we have to change the build process in VSTS. We not only have to build a database project but also the SSIS project now.

Build the SSIS project in VSTS

The first step is changing the VSTS build definition in VSTS. I've made a copy of the build definition and added some tasks.

I've added two variables for the build configuration and one for the SSIS project. I did not configure the configuration properties of the properties. I just took the defaults.

Build task
The Build task is a command line task in VSTS and there are two version 1.* and 2.*. I took the 1.* version.

This example is based on the "Hosted" agent queue

C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\

Using the "Hosted VS2017" agent has another location of the

C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\

These are the arguments I used for the build.

/rebuild $(BuildConfigurationSSIS) 
/project "$(Build.SourcesDirectory)\SSISWorldWideImporters-SSDT\SSISWorldWideImporters-SSDT.dtproj"

Copy task

Use the copy task again as I described in the database build blogpost.

Publish artifact

Publish the artifact to the Artifact repository.

You can find these artifiacts in the overall result screen of the build. These will be used in the release processstep.


Once you understand the folder structure and using the variables it is easy to write the script to build the SSIS project with



dinsdag 26 juni 2018

DevOps : Releasing your database code using VSTS (Part II)


Now, you have successfully build a Database project of your database (Part I), but now what? Off course, you have to deploy the code to an integration -, a central deployment -, or a QA environment and finally you want to deploy your code in a production environment. Now, in order to minimize the impact on production, you can introduce a staging database where you can simulate a production deployment in order to test and make 100 % sure that your code works on production.

In the diagram above, a possible release process that will work over a certain number of environments. In this blogpost I'll write some steps in order build up such an environment. I'll add more information in future blogposts. In this blogpost I'll outline some basics about setting up a release process with VSTS. The following steps are needed to build up a release:
  1. Create a release definition
  2. Choose the right artifacts
  3. Create one or more environments
  4. Add some additional options like approving

Create a release defintion

The first step is create a release definition. A release definition is an important part of your Build and release process. Below, I've included a screenshot of a release definition. Two steps are visible: choosing the artifact and where to deploy the artifact (in a so called environment) . It is possible to create multiple environments, eg. QA, staging and test.

Choose the right artifacts

In the build process we have created a artifact in the artifact repository. This is a deploy able component of a project. In this blogpost, I'll focus on Database project artifacts.  We have to specify the location in the build process and we have to use this location as an input in the release process.

It is possible to choose the latest build or specific branches.

Create one or more environments

Next step is to deploy the artifact in a specific environment. In this example I've created one environment : QA. It is also possible to create multiple environment. The Database release task : SQL Server Database Deploy requires that a Deployment Group is defined and a deployment group is collection of multiple servers. So, it could be possible to deploy to multiple servers in a QA environment (for instance).

The SQL Server Database Deply task requires some settings, like name, the servername, the database name, how to authenticate, etc.

Below a definition of a deployment group. In the example screenshot, the deployment group is off line, but when the server is online, it will indicate online. It is also necessary to execute a powershell script on the local machine to make it work (under Detail). Use Personal Access token in the script for authentication.


This is the second blogpost about DevOps and specifically releasing a database project with VSTS on a QA environment.


zaterdag 23 juni 2018

DevOps : Building your database code using VSTS (Part I)


In this blogpost I'll describe the first step by step how to create a "Build and Release" process for SQL Server database scripts : The build process. I'll include more information about releasing, SSIS, SSRS  and SSAS (tabular, MD) projects in future blogposts. This is the first blogpost about DevOps for a data warehouse and BI environment. Build and Release is often mentioned together with DevOps. Devops is short for Development and Operations. Integrating these two (different) worlds should lead to shorter development cycles. Now, Build and Release is functionality that is available on the VSTS site. In VSTS you can setup a Build first and then a Release. In order to build software you need software, right? So, there are a couple of components involved :
  • A central repository (eg. Git) where your code is stored.
  • Build definition + possible tests.
  • Release definition.
  • Deployment machine(s).
There is a difference between DevOps with normal coding and DevOps with Databases. In a normal coding DevOps Process code can be generated easily and deployed. The problem with Databases you want preserve the (state of  the) data in the database. Yet another issue is SQL Coding. There are certain statements for creating objects and other statements for altering objects.

One more difficult matter is that BI environments consists of different kind of software components. For instance, We use SSIS for ETLing the data into the different layers of a data warehouse, We use a cube for storing the data optimized for (ad hoc) analysis (with possible two different options : Multidimensional and Tabular), We have reports in SSRS or in PowerBI. All of these different tooling has to work together in order to fulfill a satisfactory experience by users.

One prerequisite is that we need to select an agent pool under Agent Queue. By default, it will show Hosted and Default (no agent pool). We need to have our own agent pool in order to queue new build. Now how to add agent queue is simple. You need to download agent for under agent pool from settings tab, configure it and run a PowerShell command.

For this blogpost, I'll use the WideWorldImporters database as an example. This is not a data ware house, off course. It is just for used for practicing Build and releasing.


In this blogpost and in the following blogposts I'll explore the best practices, can do's and don'ts, tips  and tricks of DevOps in a data warehouse and BI environment. Below, I've drawn a picture about a possible process of DevOps.

There are different strategies on how to branch your code from the master in a version control system, like Github. An easy way is to branch the code per developer and when a developer is ready, he or she will merge the code with the master branch again. 

The next step could be (automatically) build the code in a version control system (eg. VSTS). Here you can check whether the master code is still building with the new integrated branch of the developer. 

The following step in the process could be to release (deploy) the code of your project in a certain environment. This can be a simple environment or a complex environment with all kind of different machines with different software.

In this blogpost, I'll describe my experience with setting up the simple Database project with Visual Studio, Git and VSTS. 

1. Setup a Version Control repository with Git in VSTS

The first step is to set up a Github repository in VSTS, by opening an account at Here can you store your code in a centralized manner. Below I've some test projects stored. For this blogpost I use the WideWorldImporters demo database.

I've imported this database in VisualStudio and connected this project with the Github repository.

2. Setup of the Build process

The next step in the DevOps process is to configure a Build process. A simple Build process consists of four elements :
  • Select the project from your Github repository.
  • Build the (database)Project. 
  • Copy Files from the Build.
  • Publish artifacts
I'll elaborate about these steps in more detail.

2.1 Select the project from your Github repository.
First, select the repository where your code is stored, for instance Github. This will be the base for the Build task. Therefore you need to say to VSTS Release that the code is there. Select the project solution file, branch and the Team project as a source for the build process.

2.2 Build the (database)Project. 
You can build a project with a Visual studio build task or with a MSBuild task (link). There seems to be several options on how to build a Database project. In this example I choose for VS Build. Here you also select the right solution file, some arguments for the MSBuild executable.

2.3 Copy Files from the Build.
After the build is succeeded, the next step is copying the Build files. I've included fixed directory paths with some variables like $(BuildConfiguration). Other options are possible.

2.4 Publish artifact.
And finally the Build files are inserted in the Artifact folder and ready to released. This is executed with a Publish Artefact task.

Now the first phase is done and the Build process is ready for testing. We put the build process in a Queue and it can succeed or it can fail. If one of the steps fail, the build process will stop. I've included a Screen with 5 of the last builds. Some succeeded and some failed.

If you click on a successfull build you'll see something like the information on the screen below. I've some warnings due some database reference that is not properly done.

And here is a screenshot of a failed Build process


This is a first introduction in building your code with VSTS. In the near future I'll explore the VSTS Build and Release process in more depth and will continue to update this blog with more information about this.