Automating BI 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.

Prerequisites

In this post, we will learn how to build a Microsoft SSIS package in an automated way. Before we get starting doing amazing things, we need to make sure we are setup for success. SSIS packages are note 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 2019 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 is utilized 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 setup a place to house our build scripts.  So, in the root of our project, lets 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 lets open VS Code and set the project folder to be our new folder.  We are going 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.  Time start customizing this to meet our needs.

Customizing the Script

At the top of the cake file `build.cake`, lets add the following line:

#tool nuget:?package=vswhere

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-2019)

Invoking the Script

Now that the script is customized, lets check in our changes to source control and setup 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.

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

Related Posts

Sign Up for Our Blog