Client Migration with Biml

Kevin Feasel (@feaselkl)
http://CSmore.info/on/biml

Who Am I? What Am I Doing Here?

Catallaxy Services
Curated SQL
PolyBase Revealed

@feaselkl

What is Biml?

Biml is the Business Intelligence Markup Language. It is an XML-based language used to design SQL Server Analysis Services and SQL Server Integration Services solutions.

Why Use Biml?

Biml offers several advantages over native Integration Services package creation:

  • Biml XML is shorter and more concise.
  • Biml offers templating options using .NET, letting you generalize logic and script multiple, complex SSIS packages with the help of metadata.
  • Biml strips out presentation metadata, making it easier to compare in source control.

Biml is a force multiplier for ETL professionals.

Motivation

My goals in this talk:

  • Build package(s) to migrate customer data from one database to another.
  • Minimize the time I spend clicking, dragging, modifying, building, testing, and editing SSIS packages.
  • Create a solution which handles table evolution over time.
Adding a script task is easy.
Then you name it.
Then you link it.
Then you add variables.
Then you type in code.
Then you do it again and again and again all day long.

Agenda

  1. Understand the Problem
  2. Solving by Hand -- Package Walkthrough
  3. Review Available Tools
  4. Build Supporting Metadata
  5. Write the Biml Code
  6. Maintain the Biml Code

Understand the Problem

We have customer-specific data in the Adventure Works DW database, and we want to migrate this over to a new copy of the database.

To do this, we need to copy data from all tables which relate somehow to dbo.DimCustomer.

Understand the Problem

Write an SSIS package to move this data. Considerations:

  • Foreign key constraints imply precedence
  • Getting only relevant data
  • Handling existing data in new database
  • Can we safely re-run the package?
  • Will customers enter data during migration?

Agenda

  1. Understand the Problem
  2. Solving by Hand -- Package Walkthrough
  3. Review Available Tools
  4. Build Supporting Metadata
  5. Write the Biml Code
  6. Maintain the Biml Code

The Ideal Package

The first step in automation: know the task.

Building a sample SSIS package, we know we will need:

Source and Destination Connections
Expressions and Variables
A container to delete old data
A SQL task to delete old data
A container to migrate data from one server to another
A migration data flow
Source data
Destination data

The Ideal Package

The ideal package gives us an idea of what we need to accomplish, as well as important package optimizations we will need.

This stub package does not need to be perfect--in fact, we will make considerable changes as we go along. But it's easier to start when you have an end in mind.

Agenda

  1. Understand the Problem
  2. Solving by Hand -- Package Walkthrough
  3. Review Available Tools
  4. Build Supporting Metadata
  5. Write the Biml Code
  6. Maintain the Biml Code

Available Tooling

Varigance, the company behind Biml, has several tools available to help with Biml development. Get these products at their company website.

Available Tooling

Biml Express is a free extension for Visual Studio. It currently supports Visual Studio versions up to 2019.

BimlStudio is a fully-featured IDE with much more functionality than BimlExpress.

This is a paid product which offers a lot more than BimlExpress. It integrates well with Analysis Services, allows you to reverse engineer packages, and makes Biml development much easier.

BimlOnline is a beta product intended to build packages without needing to install Visual Studio at all. Its key benefit is that it can reverse engineer an Integration Services package back into Biml.

During the beta period, this product is free. After it leaves beta, there will be a price associated with it.

BimlFlex allows you to build solutions using metadata and templates, without writing code. This is also a paid product and can be quite expensive, but can help with rapid warehouse development.

BI Developer Extensions

In addition to Varigence products, BI Developer Extensions (formerly BIDS Helper) has the ability to generate packages from Biml.

BI Developer Extensions is available in the Visual Studio Gallery and on GitHub.

Agenda

  1. Understand the Problem
  2. Solving by Hand -- Package Walkthrough
  3. Review Available Tools
  4. Build Supporting Metadata
  5. Write the Biml Code
  6. Maintain the Biml Code

Build Supporting Metadata

We could write Biml using hard-coded table names and hand-crafted copy-paste code, but that's not very efficient at all. Let's create a metadata table to store everything we'll need for later.

Metadata Requirements

  • Object details (schema name, table name, etc.)
  • Foreign keys to determine table load order
  • Table columns and basic select operation for migration
  • Join details to get from DimCustomer to our table
  • Where clause to filter out our customer

Generating Migration Data

For a small number of tables, you can easily generate migration data by hand. This is a one-time operation. For a larger number of tables, you might want to script it out.

Demo Time

Agenda

  1. Understand the Problem
  2. Solving by Hand -- Package Walkthrough
  3. Review Available Tools
  4. Build Supporting Metadata
  5. Write the Biml Code
  6. Maintain the Biml Code

My First Biml

Biml is just XML. It all starts with a Biml tag:

Connections

We can define connections in a separate file to make them easier to find. Biml lets you combine files together after the fact.

Templates

Templates let us group together files into "tiers." The Biml engine processes tiers in ascending order, so files with tier="1" run before tier="2" and so on. You do not need to number your tiers sequentially--you might want to leave gaps for future growth.

In our code, connections is tier 1 and the project/package file is tier 2.

Fleshing Out Our Tasks

A simplified form of the package:

CallBimlScript

The CallBimlScript function lets us reference a separate Biml file. Here, we use it to decide between a delete-and-reload strategy and an incremental load approach. In other places, you can use CallBimlScript to abstract out common functionality.

Let's take a look at the code.

Demo Time

Agenda

  1. Understand the Problem
  2. Solving by Hand -- Package Walkthrough
  3. Review Available Tools
  4. Build Supporting Metadata
  5. Write the Biml Code
  6. Maintain the Biml Code

Metadata Maintenance

As tables change, we should keep the metadata we created up to date.

To support table changes, we just need to re-generate the packages from the Biml script we created. No additional changes are necessary.

Package Changes

If we want to change the package(s) we create, we can modify the Biml directly.

As the Biml scripts change, we just need to re-generate packages from the scripts. If you built your Biml correctly, you should not need to modify your packages directly.

Treat the packages as binaries and Biml as source code.

Source Control

You should store your Biml in source control. With the Biml in source control and a Biml-first approach with no post hoc SSIS package changes, packages do not necessarily need to be checked in.

An advantage to Biml over raw SSIS packages is that you can perform meaningful diffs against Biml scripts, something you cannot do with SSIS packages.

Wrapping Up

Biml is a powerful language. We've seen one use case in which Biml saved dozens of hours of development and maintenance time.

With small tweaks, I've implemented this several times in different guises.

Wrapping Up

To learn more, go here:
https://CSmore.info/on/biml


And for help, contact me:
feasel@catallaxyservices.com | @feaselkl


Catallaxy Services consulting:
https://CSmore.info/on/contact