VSTS Continuous MSBuild for DWH / BI

Wait, didn't I post this already several weeks ago? Well, almost. A few weeks ago, I showed how to set up a build agent using devenv.com. Unfortunately, I ran into some problems like failing builds not reporting failure and SSAS Tabular projects not building correctly1. However, it turns out to be pretty easy to build almost everything2 using MSBuild. Here's how.

The solution I want to build in VSTS (using MSBuild) consists out of the following project types:

  • SSRS
  • SQL Server
  • SSAS Tabular
  • SSIS

SSRS

I haven't got a running SSRS build yet. I don't need to: the build artifacts resulting from a SSRS build are exactly the same as the "source files". So why not just copy the source files? However, I do have some interesting scripts to automate SSRS deployments - if you're interested just PM me (or wait until I post something about these scripts).

SQL Server; SSAS Tabular

When calling the MSBuild task from within VSTS (Visual Studio Team Services), SQL Server as well as SSAS Tabular already builds out of the box.

SSIS

SSIS does NOT build out of the box with MSBuild. However, you can build a library that does. These are the steps you need to take - it's actually pretty easy:

  1. Download the source of SSISMSBuild
  2. Open the project, fix the references
  3. Add a new self-signed key
  4. Build
  5. Include libraries in source control:
  6. Create a .proj-file that calls on the SSISMSBuild library
  7. Add a separate MSBuild task in VSTS building all SSIS projects

Download the source of SSISMSBuild

You can do so here: http://sqlsrvintegrationsrv.codeplex.com/SourceControl/latest.

Remember to check if no files are read-only before continuing!

Fix references

Open the .csproj file in Visual Studio. Depending on the version of SQL Server installed on your machine, libraries might reside on another place. Add the missing libraries in each project via "Add Reference..."

I needed to fix three references before I could build the library. In my case, all were located at C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio:

  • Microsoft.AnalysisServices.Project
  • Microsoft.DataTransformationServices.VsIntegration
  • Microsoft.DataWarehouse.VsIntegration

After fixing the references, you need to change the framework of the C# project accordingly: by default, it's set to .Net 4, while my SQL Server 2016 libraries are built against .Net 4.5. You can do so by opening the project properties, and changing the "Target Framework" setting under the tab "Application":

Add a new self-signed key

As you might notice, there's a missing "key.snk" file in the project.

You need to create a new key to sign the library. The easiest way is opening the project properties, click "signing", then choose to create a new key name file:

Give it any name you like, choose a password and off you go. The key is now called "key.pfx" instead of "key.snk", and you can delete the project reference to the non-existing "key.snk" file.

Build

Well, I expect you to know how to do that.

Include libraries in source control

The project file building your SSIS projects will need to reference some of the dll files created by the build. You'll need these five at least:

  • Microsoft.AnalysisServices.Project.dll
  • Microsoft.DataTransformationServices.VsIntegration.dll
  • Microsoft.DataWarehouse.dll
  • Microsoft.DataWarehouse.VsIntegration.dll
  • Microsoft.SqlServer.IntegrationServices.Build.dll

Group them in one folder, and add them to source control. I've chosen to bring them into the folder $/(Team Name)/CI/SqlServer.IntegrationServices.Build/.

Create a .proj file

We won't let MSBuild handle with the .dtproj files directly. Instead, we let MSBuild handle a generic ".proj" file that accepts a reference to the '.dtproj' file as an argument and tells MSBuild how to handle the '.dtproj' files.

Below is my personal version - note that

  1. I've used several placeholders here, so I need only one MSBuild-proj-file for my +/- thirteen SSIS projects
  2. I'm placing this file inside my solution folder. This means every SSIS project has its own subfolder below the '.dtproj' is located

Add a separate MSBuild task to build all SSIS packages

In my build definition, I've included two steps for building:

  1. The default Visual Studio Solution build
  2. The custom SSIS build, executed via PowerShell

The default VS Solution Build step uses MSBuild behind the scenes, so there's no devenv.com-magic going on here. It will build SQL Server and SSAS Tabular out-of-the-box, but won't build SSIS.

The custom SSIS build will solely build the SSIS projects. I'm executing MSBuild here via PowerShell, so I only need one task for all SSIS projects (even new ones currently not included)

Here's the PowerShell script3

One more thing: SSAS Tabular unattended deployment

Above, I've shown you how to do an automated (unattended) build for SQL Server, SSAS and SSIS. This will create build artifacts which you can include in an (automated? unattended?) deployment step, bringing you one step closer to Continuous Integration.

However, in order to do an unattended SSAS Tabular deployment, you'll need not only a .asdatabase, but also three other files:

  • .configsettings
  • .deploymentoptions
  • .deploymenttargets

These three tell for example about the data source settings, impersonation, processing immediately after deployment, retaining security roles or not, and targets. You can easily generate them by running the ASDeployment wizard in output mode answer mode.

Be aware that every environment and use case will result in a different set of desired settings for deployment! Therefor, I've included for all desirable scenarios (D, T, A, P, UAT, etc.) a folder describing that scenario. I've placed them inside the aforementioned 'CI' folder, and copy them as build artifacts. As a result, my deployment agents will always have all possible scenarios at hand for automated deployments4.

Founder of this blog. Business Intelligence consultant, developer, coach, trainer and speaker at events. Currently working at Dura Vermeer. Loves to explain things, providing insight in complex issues. Watches the ongoing development of the Microsoft Business Intelligence stack closely. Keeping an eye on Big Data, Data Science and IoT.

Leave a Reply

4 Comments

  1. Jeremy Brown

    This is fantastic work. You have no idea how this has been a life saver for me in my project. Thank you!

    Also, I think I found an error. Above, in your last statement on automated AS deployments you mention "You can easily generate them by running the ASDeployment wizard in output mode."

    Looking at this, don't you mean running it in ANSWER mode? In answer mode, it stores the settings, including the encrypted passwords which are VERY important for automating the deployment across different environments.

    Cheers!
    -JT

    • Hi Jeremy,

      Sure - you're right. It should be answer mode. Thanks for pointing that out!

      Cheers,

      Koos

  2. Marvin Schenkel

    Great post. I use a slightly different approach where I install Data Tools on the build server and use customer MSBuild scripts to build my SSIS, SSAS, SSRS and database projects. To complete the circle of Continuous Integration, I have developed a Powershell script that is able to deploy the artefacts the build creates for you. This script uses SQLPackage and ISDeploymentWizard to deploy dacpacs and ispacs.

    You need a private build controller (hosted on-site) in order to contact your servers. The final step of the build process will kick off a load on our CI-environment to load a minimized staging set all the way through to our data marts.

    Maybe this will help!

    Cheers,
    Marvin

    • Hi Marvin,

      I tried using MSBuild in a custom installation as well. Currently I favor the built-in deployment options of VSTS, although I didn't have the time to check out all possibilities writing the MSBuild scripts manually :).

      A third way is to use TeamCity, OctopusDeploy and the likes. I know some places where this is used as well.

      Cheers,

      Koos

Next ArticleAzure Data Factory, first thoughts