Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Introduction to the Biml Language



By:
Overview

Biml or Business Intelligence Markup Language is an open-source XML dialect to generate Integration Services (SSIS) packages.

Biml can be used to create multiple SSIS packages from metadata and eliminate much of the manual work that is normally required in the Visual Studio designer. Biml can assist in data warehouse scenarios, data integration/migration projects or every project where a number of SSIS packages who follow the same pattern need to be created.

When to use Biml

Biml works best if you need to create multiple SSIS packages that all follow the same pattern. In other words, if all packages have roughly the same objective and structure, but maybe only the source and destination table change. The changing “data” between packages we’ll refer to as metadata.

Examples of patterns are:

  • Loading a (persisted) staging environment
  • Loading data into dimensions or fact tables
  • Creating a raw data vault

When not to use Biml

If you only need a handful of packages, it might not be worth it to write the Biml scripts first to generate those packages, especially if you can easily create all those packages by hand in less time. Or if you need to create one complex, unique package for a single occasion, Biml is certainly not the right tool for the job.

Outline

In this tutorial, we will go over the basics of the language and show you how you can generate multiple packages at once. At the end of this tutorial, you will be able to create Biml files that can automatically create packages to stage data from one server to another. The tutorial assumes you already know what SSIS packages are and how they work. If you need a refresher, you can take a look at the SQL Server Integration Services (SSIS) Tutorial or the SQL Server Integration Services SSIS 2016 Tutorial.

The high-level outline is as follows:

  • Available tools and installation
  • Biml language basics
  • Generating a simple package
  • BimlScript basics
  • Retrieving metadata with GetDatabaseSchema
  • Dealing with relational objects
  • Biml and the layered approach
  • Creating the destination tables
  • Generating multiple packages at once

You can use the outline on the left to browse the tutorial or use the arrows “Previous” and “Next”.

Additional Information

Last Update: 2/8/2018




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools