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 devenv.com. 
  • 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 : http://abelsquidhead.com/index.php/2016/10/06/build-and-deploy-ssis-packages-using-vsts/

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\devenv.com

Using the "Hosted VS2017" agent has another location of the devenv.com

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

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 Devenv.com.



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 VisualStudio.com. 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. 


woensdag 28 februari 2018

Azure series : Building an Azure - SQL Server - Analytics box with VirtualBox (revisited)


Back in 2012, I wrote a blogpost about building a SQL Server playground with VirtualBox. Now, I want to build another VM with Windows Server 2016, Visual Studio Professional 2017 (and 2015) and SQL Server 2017. So, this blogpost is an upgraded version of the earlier blogpost I wrote. Well, it is not as extensive as I wrote earlier. I'm not building a Domain Controller, etc, but just a simple Development Box for business intelligence, analytics, data science and data management with SQL Server, Visual Studio, Excel and PowerBI. I'll also focus more on Azure and install tools for integration with Azure.

The steps I'll perform and are described in this blogpost are:
  1. Create a VirtualBox environment.
  2. Install windows Server 2016.
  3. Do some additional actions and sysprep the environment.
  4. Installation of Visual Studio 2017 (Version 15.5).
    • Installation SSDT BI templates for VS 2017.
  5. Installation of Visual Studio 2015 Update 3
  6. Installation of SQL Server 2017.
    • Please pay attention for installation of JRE 8 (and not 9).
  7. Installation of SQL Server Management Studio (version 17.4).
  8. Installation of sample databases like WorldWideImporters samples.
  9. Installation of Office 2016.
  10. Installation of PowerBI Desktop latest version
  11. Installation of R Studio (now version 1.1423).
  12. Installation of R Client for Windows (version
  13. Installation of R Tools for Visual Studio (version 1.1).
  14. Installation of Azure Storage Explorer.
  15. Installation of Visual Studio Cloud Explorer (both VS2015 and VS2017).
  16. Installation of Dax Studio (now version 2.7.4) and DAX editor for VS.
  17. Installation of PowerPivot utilities (version 1.09).
  18. Installation of Redgate SQL Search.

Other suggestions are installing the tools BIMLExpress, BIDSHelper and Vertipaq analyzer.

Setting up the VirtualBox

First, install VirtualBox. Next, press on the New button to start the configuration of a new VirtualBox environment.

Give the VM a proper name and set the right properties.

Create a Harddisk for the VM.

I decide to use the VDI image of VirtualBox. In the past I used the vhd for building the harddisk, but the VDI is native supported harddisk type of VirtualBox.

Let's check the Expert modus but nothing really needs a change. 

And choose for Create for building the Virtual Machine.

Note: I've started with 50 GB but I had to expand to 75 GB and now the disk is too small again.

Installation of Windows 2016

I've downloaded Window Server 2016 from my Visual Studio Subscription and used it for the installation of Windows on the VM. 

I decide to use the Desktop experience. 

Some settings are set and the installation proceeds

Additional steps

3) Run Windows Update

  • Disable IE ESC.
  • Remote desktop enabled for administrator.
  • Disable expiration for administrator passwords
  • Turn off windows login/logoff sounds
  • Update folder options to show all files and extensions
  • Add desktop icons for Computer, User files and Recycle bin

1) Install the Guest Additions by choosing the menu apparaten and "Insert Guest additions CD Image" and go to the dvd drive with Windows explorer.

2) After this installation, the VM needs a restart so that's what we do next. The VM is restarted and the next step is to sysprep the VM again. In this way, we can create a BASE VM for future usage. 

And it's processing the Windows intallation with the sysprep tool.

I created a clone of the VM machine. Now, I've always a VM with Windows Server 2016 available for new installations of application software.

Installation of Visual Studio Professional 2017

The next step in the process of building a analytical box is installation of Visual Studio Professional. The newest version is Visual Studio 2017. At first, I started with Visual Studio 2017 but some VS templates are not available on 2017. Therefore, I decided later on that I had to install 2015, too.

The next step is to choose the workloads in the Visual Studio installer. For this installation I choose to install : 
  • Azure Development with Azure Data Lake and Stream analytics tools, Cloud services tools, etc
  • Python Development. Off course this includes Python support, but also Azure Cloud Services Core Tools.
  • Data and Storage processing. The following optionals are installed : F#, Pyhon and R support, etc.
  • Data Science and Analytical Applications. This includes tools like Python language support, Microsoft R client ((3.3.2) and R Language support.

And now press on Install and the installation process is started:

Now let's see what kind of templates are installed in Visual Studio:
  • SQL Server
    • SQL Server Database Project
  • R
    • R Project
  • Azure Data Lake
    • HIVE projects
    • PIG projects
    • Storm projects
    • U-SQL projects
  • Stream analytics
    • Stream analytics projects
  • Python
    • All kind of projects like, Web projects, Django projects, IronProject, etc

Next step in this VM building process is the installation of SQL Server Data Tools for Visual Studio 2017. 

Regarding Data Lake and Data lake analytics please note that after installation:
  • The Server Explorer > Azure node contains a Data Lake Analytics node.
  • The Tools menu has a Data Lake item.

Installation of Visual Studio 2015

During the installation of Visual Studio 2017 I noticed that the Azure Data Factory V1 templates are not supported and I wanted to use these for working with Azure Data Factory for the course Orchestrating Big Data with Azure Data Factory on Edx. Therefore I installed the VS2015 also. Alhough it is not recommended to install VS2015 after VS2017, in my case I did not have choice, otherwise to deinstall VS2017 but that was something I could do anyway.

And now the installation of the Azure Data Factory Visual Studio 2015 Templates is possible.

And now I can create Azure Data Factory projects for Azure Data Factory V1.

In the meantime I installed also the BI templates for Visual studio 2015, so now I can develop the SSIS, SSAS and SSRS in VS2015 as well as in VS2017.

Installation of SQL Server 2017 Developer edition

The next step in the processbuilding a new playground VM is the installation of SQL Server 2017. So I downloaded SQL Server 2017 Developer edition and selected all the options.

And because the PolyBase connector is selected in the feature selection the following error occurs.

So, let's install the latest version of the Java engine (v9) and all problems are solved...Well, that is not the case. The error keeps coming and this seems a bug in the installation process. You have to install version 8. And so I did and the error is disappears after the installation of Java update 8.

And after a couple of times Next, Next, Accept en Next the installation process continues.

Installation of SQL Server Management Studio 17.4

In order to work with the Database engine and other featrues of SQL Server you have install the SSMS too. So, I downloaded the latest version of SSMS.

Check regularly for new updates of extensions and VS templates.

Installation of Samples databases

Next step was the installation of the sample databases like WorldWideImporters and AdventureWorks, both OLTP and DW. Support for Adventureworks is stopped, but examples of queries on MSDN are still based on AdventureWorks. That's why it is good practice to install AdventureWorks too.

Installation of Office 365

Now, an important item for data analysis is (still) Excel and that is available in the Office365 subscription. I decide to install a version of Office 365 in my VM. This way it is easy to browse the Tabular models in my VM and the Tabular models I develop in Azure Analysis Services.

Installation of PowerBI Desktop

Yet another tool that is indispensable nowadays is PowerBI Desktop. For this reason a Microsoft analytical VM box without PowerBI Desktop is not an Analytical Box;-)

Installation of  R Studio

Although Visual Studio also support R now, I do find it convenient to use R Studio for building R scripts. Most of the time I build, test and run R scripts in R Studio and there is a lot of support for other tooling like Swirl, R Markdown, R presenter, Shiny apps. I don't think that these are supported in Visual Studio. May be that is for future blogposts!

Microsoft R Client on Windows

It can be handy to use the Microsoft R Client on Windows for analysis. Therefore I install this client too. The current version is

It will take some time to install.

Installation of R Tools for Visual Studio

Next is using R in a IDE like Visual Studio. Download R for Visual Studio and install the software. Using R in Visual Studio happens by creating a R project.

Installation of Azure Storage Explorer

For exploring, uploading, downloading, and to manage blobs, files, queues, tables, and Cosmos DB entities it is a good idea to install the Azure Storage Explorer. Current version, at the moment of writing, is 0.9.4. (january 2018).

Installation of cloud explorer

The next thing that useful is the ability to browse the Azure environment with the cloud explorer in Visual Studio 2015 and 2017. Download these from the marketplace: VS2015 cloud explorer  and VS2017 cloud explorer.

Installation happens with a vsix file and double clicking is enough. At first glance, it seems that the installation was only on VS2017, but the Cloud explorer is installed both in VS2015 and VS2017. The Cloud Explorer vsix for 2017 seems not necessary (!).

Installation of Dax Studio and DAX editor for Visual Studio

Another useful tool is DAX Studio. This tool will help you build and analyze DAX expression. With DAX Studio it is also possible to connect to Azure Analysis Services. Please verify during the installation of DAX Studio to check the option "Ensure that the pre-requisite for analysis Services Azure are installed"

Connecting to Azure Analysis Services works!

The installations of VS DAX template happens with the known, so called vsix files. Double clicking the file will easily install the editor in Visual Studio 2015 (2017 is not yet supported).

So, that is finished up too and now up to the next tool, PowerPivot utilities.

Installation of PowerPivot utilities

The installation of PP utilities is easy. In the accompanied README file the instructions are well explained. Copy the file to a desired location and integrate this in Excel with the menu Options and Add ins.

Important note is that PowerPivot is not included in all versions of Excel 2016. For instance, Excel Home Edition does not support PowerPivot.

Installation of SQL Search

A useful tool to search your database is SQL Search from Red Gate. This is a free tool and there are also other tools available.

The current version doesn't work with SQL Data warehouse, unfortunately.


At the moment of writing, the Azure Data Factory V1 Templates are not compatible with Visual Studio 2017. Only VS2015 is supported. Now, most of the times you want the latest version of software. But, because Azure Data Factory is only supported in VS2015, I should have installed VS2015 first (as adviced by Microsoft) and then VS2017. Installing an older version after a newer version of Visual Studio is not really a good practice. So, my advice for building VM's with Visual studio would be to install always the older version first and then the newest version of Visual Studio.

Ath the moment of writing Azure Data Factory V2 is out now but the ADF templates in VS do not work with ADF V2. It's not clear to me if Microsoft will continue supporting ADF Templates in visual Studio.