Martin Andersson

Integration with a Custom SSIS Execution Framework

Thus far, you’ve learned quite a bit about creating “build-time” frameworks with Biml code that govern how your solution is generated and the coding patterns that you enforce. What about frameworks that govern your runtime behaviors such as orchestration and package execution order? The wait is over!

Biml and Analysis Services

This chapter assumes that you’ve already familiarized yourself with the basic concepts of Biml. We will focus on the additional elements and attributes needed for Analysis Services projects.

Documenting Your Biml Solution

It provides a high-level view of the database schemas, system architecture, and other important design considerations to all of the stakeholders and users of a data system. It also serves as an important reference for analysts as they work to understand the intricacies of column/attribute values and their relationships.

Using Biml as an SSIS Design Patterns Engine

It might not have been obvious, but your code was implementing a specific SSIS design pattern using your metadata and BimlScripts. With the same metadata and modified BimlScripts, you could implement totally different patterns with altered semantics, performance characteristics, and much more. After implementing multiple such patterns, you might even want to create a mechanism to select the desired pattern for a given transformation through the metadata itself. In fact, let’s go ahead and do that right now.

A Custom Biml Framework

In this chapter you are going to build a moderately complex solution: a custom Biml metadata-driven framework.

Parallel Load Patterns in Biml

In Chapter 7, we demonstrated an approach for modularizing data load patterns within a metadata-driven Biml framework so that we could easily select which pattern to use on a table-by-table basis. As our focus was on the modular pattern architecture, we presented only two interchangeable patterns for loading data. Of course, there are countless other patterns that you could choose to author and customize for your organization. Ultimately, you will need to decide which pattern or patterns will work best for your organization and either write new or customize existing Biml code to implement your solution.

Reusing Code, Helper Classes, and Methods

For a single project, this might be all you need. However, once your solution grows and you need to use Biml for more complex scenarios, you may start to see some of your code repeated in multiple files.

Biml for T-SQL and Other Little Helpers

Consider the following scenario: you would like to generate data and create or alter statements as part of the same build process. This is doable if you are using continuous integration with Visual Studio but not as easily achieved if you are developing using SSMS. In this chapter, we will show examples of how this can be done with Biml, whether you are using BimlExpress or BimlStudio.

Introduction to the Biml Language

Biml is an XML-based language that allows for faster development by creating more consistent and reliable code. Business Intelligence Markup Language (abbreviated as uppercase B, lowercase i, lowercase m, and lowercase l) uses XML along with code nuggets written in C# or Visual Basic to automate SQL Server Integration Services, Analysis Services, and Relational Database Objects.

Advanced Biml Frameworks and BimlFlex

As with any development technology, your Biml projects will start small and grow over time. At first, all you care about is getting the basics to work, but as you find additional corner cases and incrementally add features, you’ll find that the growth of your codebase brings new challenges.