How to Automate Business Intelligence in ETL Builds

As we start to reap the fruits of integrating build automation into our software projects, I was asked if we can apply the same concepts to other aspects of the business; specifically business intelligence.

This came from a requirement to remove humans from manually deploying the ETL model to production databases. But more than just meeting requirements can be gained from this.

All the benefits of build automation, as it applies to our software products, can be enjoyed in our business intelligence projects as well. This post is the starting point as we bring the power of DevOps to BI.

Business Intelligence Prerequisites

In this post, we will learn how to build a Microsoft SSIS package in an automated way. Before we get started doing amazing things, we need to make sure we are set up for success. SSIS packages are not built in the same way as other code projects are. SSIS doesn’t use MS Build to create binaries.

Instead, it uses the devenv.exe (VS IDE) and the installed extensions. SSDT for visual studio 2022 has moved to using SSIS extension found on the marketplace. However, whatever you installed on your local machine, is what should be installed on the build machine. If you can use the SSIS project extension, I would recommend that, as it is still getting updates and problems are constantly being addressed and fixed.

Setting up the Script

Cake is a powerful platform for writing automation scripts. It utilizes the C# language and is very versatile. If you are familiar with C#, then you will be right at home with Cake. Setting up cake is relatively simple and there are VS Code extensions to make this even easier.

First, we need to set up a place to house our build scripts. So, in the root of our project, let’s create a folder called build. Here we will store our PowerShell bootstrapper, our cake build script, and any additional resources needed to build our project.

Now let’s open VS Code and set the project folder to be our new folder. We are going to install the Cake extensions and use it to install a bootstrapper and sample build file. Installing the files is done by opening the vs code command pallet (CTRL + p) and typing cake. The cake commands will be listed and we can select “Install bootstraper” and “Install sample build file”. This will be our starting point, and now it’s time to start customizing this to meet our needs.

Customizing the Script

At the top of the cake file `build.cake`, let’s add the following line:

This will ensure that the VSWhere package is installed and available so that it can be easily used in our build script. Now we start writing our logic to build our project. We need to capture the path to our SSIS solution or project file and store it in a variable for future use.

This should be a relative path to our cake script, as we will not know the absolute directory path when this runs on our remote machine. It is a good idea, as we capture these paths, that we print them out either in a log, or on screen, so that we can make sure we are getting the right paths and debug any issues we might run into.

Next, we need to get our Visual Studio IDE path captured to be used later. This is where we will use VSWhere and store the output.

var projectFileInfo = new FileInfo(projectPath)
Information($"{projectFileInfo.FullName}");
Information($"Project Exists: {projectFileInfo.Exists}");
if(!projectFileInfo.Exists) {throw $"project file not found at {projectFileInfo.FullName}"}

Next, we invoke the IDE process using cake’s built-in function.

var logfile = new FileInfo($"{projectRoot}/BuildLog.txt");
using(var process = StartAndReturnProcess(processPath, new ProcessSettings{ Arguments = $@"""{projectFileInfo.FullName}"" /build ""Development"" /out ""{logfile.FullName}"""}))
{
   process.WaitForExit();
   // This should output 0 as valid arguments supplied
   process.GetStandardOutput().All(x=>{Information(x); return true;});

   if(process.GetExitCode() != 0)
   {
      Information("Exit code: {0}", process.GetExitCode());
      process.GetStandardError().All(x=>{Error(x); return false;});
   }
}
Information("BuildLog:");
Information($"{System.IO.File.ReadAllText($"{logfile}")}");

Above we build the project in development mode. This directly corresponds to the configuration options set in the project. If you have a configuration by a different name you wish to use then use that. 

If you need to switch between them, then you can use the configuration variable/parameter set at the top of the cake script. We also write the output of the IDE build to a file so that we can parse that and print the results of the build.

For more options, the documentation of the command line arguments can be found on Microsoft’s documentation (https://docs.microsoft.com/en-us/visualstudio/ide/reference/devenv-command-line-switches?view=vs-2022)

Invoking the Script

Now that the script is customized, let’s check in our changes to source control and set up our build steps. The easiest thing to do is to invoke our PowerShell bootstrapper script. This will download and install all the requirements needed, and then invoke the cake script. This can be done locally to test and make sure you have your relative paths correct. Once you are reliably building, transition to having your remote build server invoke the script.

Make sure your build server has Visual Studio IDE installed, not just MS Build, along with the SSIS Data Tools extension. From here just work through the bugs. Everyone’s environment is a little different. Once your builds are successful, we can start trigging build automatically and capture the artifacts for later deployments.

Business Intelligence – Finishing Up

That’s it for building the package. The output will be in the same location as if you built the project normally from the IDE. This is typically found in the bin/configuration folder of the project. Now the ISPAC is on the build machine. It’s not doing anyone any good there.

We now need to get this off the build machine and into a deployment cycle. This can be accomplished in many ways. A build service, such as Azure DevOps, will have steps that will let you upload artifacts of the build to be tracked and used in future deployments. Or you can script it all out inside the cake script. Depending on your needs either way is appropriate.

Originally published August 24, 2020. Information refreshed December 21, 2022.