If you want to set up Continuous Integration using Visual Studio Team Services (VSTS) in order to build your BI projects, you'll run into the fact that some project types can be built and released out-of-the-box by MSBuild, while others need specific logic to create or deploy artifacts. In this post, I share my current folder structure for BI projects, which enables me to use out-of-the-box MSBuild functionality for project types supported, and custom build steps for specific project types.

If you want to learn even more about Continuous Integration in VSTS for BI projects, please see Azure DevOps voor Data Engineers

When developing BI solutions in Visual Studio, there are many ways to structure the different "parts" and project types. For example: some smaller (and more PoC-like) projects, have all project types (databases, ETL, reports and cube) inside one "supersolution", so that dependencies can be handled by Visual Studio as much as possible. In larger companies (where BI solutions are often split up into layers or tiers), database layers are often treated as a different "product" in separate repositories, which enables more modular development (and increases the need for continuous integration).

When structuring my solutions inside a repository, I currently try to group by project type in my folder structure. For example:

• SQL Server projects (.sqlproj):
  • \SQLServer\DW\DW.sqlproj
  • \SQLServer\DataMart\DataMart.sqlproj
• SSIS projects (.dtproj):
  • \SSIS\IngestETL\IngestETL.dtproj
  • \SSIS\ExportPackages\ExportPackages.dtproj
• SSAS projects (.smproj for tabular)
  • \SSAS\MyTabular\MyTabular.smproj

This way, when I set up the build recipe within VSTS, I can point MSBuild to the solution files for project type that build out of the box. It requires me to create solutions for every project type, but IMHO that's a good thing[ref]The only moment you really *need* build dependencies managed by Visual Studio inside a BI project is when databases refer to each other - and that's still possible, because two SQL Server databases are of the same project type.[/ref]

Basically, my solutions for the structure above would look like this[ref]Of course the `databases.sln` could as well be split up into two solutions[/ref]:

• SQL Server projects (.sqlproj):
  • \SQLServer\databases.sln
    • \SQLServer\DW\DW.sqlproj
    • \SQLServer\DataMart\DataMart.sqlproj
• SSIS projects (.dtproj):
  • \SSIS\IngestETL.sln
    • \SSIS\IngestETL\IngestETL.dtproj
  • \SSIS\ExportPackages.sln
    • \SSIS\ExportPackages\ExportPackages.dtproj
• SSAS projects (.smproj for tabular)
  • \SSAS\MyTabular.sln 
    • \SSAS\MyTabular\MyTabular.smproj

Structuring my solutions this way makes it easy to make technology-specific build steps which can then easily be applied to other repositories with the same project types. Build steps for the example above would look like this (I've left out the "copy build artifacts" and "publish" steps):

SSAS Tabular & SQL Server build steps

SQL Server projects work natively with MSBuild (as long as the build environment has SQL Server Data Tools installed). Therefor, this can be a normal MSBuild build step for SQL Server and SSAS Tabular (the one displayed below is for SSAS-TM):

SSIS Build Steps

SSIS does not (yet) build out-of-all-boxes. I'm planning to elaborate on this point in a separate post, but for now this is all you have to know to get started:

1. I'm using a separate build tool that's available from NuGet
2. As a result, there have three build steps for SSIS
   1. Install NuGet
   2. Use NuGet to install the tool
   3. Call on the tool to build SSIS projects

Install NuGet

The NuGet tool installer ensures that a recent version is available on the build agent machine. In this particular case, version 4.3.0 is installed, that was the most recent version at the moment I created this recipe. Any recent version will do.

Use NuGet to install the tool

With the NuGet custom command "install" I can manually install tools inside the working directory. In this case, SSISBuild is installed in order to build the SSIS projects. The tools will be downloaded into BUILD_SOURCESDIRECTORY (which is available as an environment variable).

Call on the tool to build SSIS projects

In order to build the projects, I use a small PowerShell script. This can be a script file (stored inside version control), but an inline script will do as well:

Get-ChildItem -Filter *.dtproj -Recurse | Where-Object { -Not ($_.FullName -match "obj") } | ForEach-Object {
  &"$($env:BUILD_SOURCESDIRECTORY)\SSISBuild.2.1.2\tools\ssisbuild.exe" $_.FullName -Configuration Release -ProtectionLevel DontSaveSensitive
  if ($LASTEXITCODE -ne 0) {
    throw "Build failed.";
  }
}

Notice:

1. The PS script is meant to build all SSIS projects
2. The "SSISBuild" package is located in $($env:BUILD_SOURCESDIRECTORY)\SSISBuild.2.1.2. This is the place where NuGet downloaded the package
3. Currently, the build configuration is always set to "Release"

One More Thing

Or wait, maybe three.

1. Keep in mind that a solution is only a list of projects - so if I prefer to work with solutions containing other combinations of project types, I can add extra solutions. I just need to add them to a place where my own wildcard search of MSBuild doesn't find them (and it'd be nice to have some kind of standard structure where I place these extra solutions as well)
2. Don't think too much about the "problem" of scattering your solution files. After opening a repository in Visual Studio, it shows you the list of available solutions, remember?
3. I'm using SSISBuild for SSIS build and deployment. There are a two caveats here (other than that, it's a very easy way of building and deploying SSIS packages):
   1. It hasn't been updated for over a year (but still works great)
   2. It expects your ".dtproj.user" file to be present as well (usually these are ignored by your Git/VSTS repo)

More Resources

With this folder structure in place, it's not too hard to customise build steps for some project types and re-use them in other repositories as well. Besides my earlier mentioned Training: Continuous Integration in BI using VSTS and Git (linked page currently in Dutch, but the training can be given in English). I've also written something about this subject in the past, which might be interesting as well:

• Blog: VSTS Continuous MSBuild for DWH / BI
• Blog: VSTS Continuous Build for DWH / BI