Month: April 2015

BIDS Helper, Biml, Microsoft Technologies, SSIS

Beginning With Biml

Meagan Longoria

I have been learning and using Biml for several months, but I neglected to blog about it until now. If you are using SSIS and are not familiar with Biml you need to check it out. Apologies if this post sounds like an ad for Biml, but if you read down to the Benefits of Biml section you will see how learning just a bit of Biml was extremely useful for me. It is a technology that I find exciting and effective.

What is Biml?

Biml, created in 2008, is a domain specific language for describing business intelligence objects. It’s essentially XML that you can write to build SSIS projects faster with fewer mouse clicks and while ensuring consistent design patterns across packages and projects. Biml can describe the following objects:

  • SSIS packages
  • databases
  • schemas
  • tables
  • columns
  • SSAS cubes, facts, and dimensions (Mist only)

Most people who write Biml extend it with BimlScript. BimlScript allows you to use C# or VB.Net to extend your Biml to perform tasks such as:

  • quickly import database table schemas
  • replace static values with expressions
  • include text from another Biml file or text file
  • turn tedious, repetitive work into reusable scripts

While Biml is useful without BimlScript, it is much more powerful with it.

Get Biml

To use Biml, you need either the BIDS Helper add-in for SSDT-BI or Mist. BIDS Helper is free and has everything you need to write Biml for SSIS. This is a great way to get started. If you already have BIDS Helper, make sure it is version 1.7+ as there were several breaking changes related to Biml released in version 1.7 (and you don’t want to learn the old way and then have to learn the new way for the current version).

Mist is not free, but there is a free 14-day trial available. Mist has more Biml capabilities, such as reverse engineering Biml from existing SSIS packages and describing SSAS databases. Mist also offers a GUI if you want the benefits of Biml without having to write all the XML yourself.

Start Small

Once you have BIDS Helper or Mist, you are ready to write some Biml. I am not nor have I ever been a .Net developer, although I think I pretended to be one for a couple of semesters in college. I have made a few simple .NET websites and written some C# for SSIS script tasks, but that is not really where my strengths or interest lie. Yet even I have learned to love writing Biml, and I am working on getting better at BimlScript. I fully admit I have spent a few hours banging my head against the wall trying to figure out how Biml works, but that is how I learn. I was able to make progress because I started small, getting the hang of the basic building blocks and then adding new pieces of knowledge to my foundation. If you need a plan with some quick “aha” moments to make you feel accomplished on your journey of learning Biml, you can follow my initial learning path.

  1. Create a blank package in Biml and generate it using BIDS Helper
  2. Create a project level connection in Biml and generate it using BIDS Helper
  3. Create a package to populate a staging table in Biml with BIDS Helper that simply truncates a table and then selects data from a single table in another database and inserts it into the newly truncated table.
  4. Use Mist (the free trial, if necessary) to reverse engineer a dimension package and a fact package that follows your desired SSIS design patterns. Examine the Biml that is created, and then try recreating them by hand.
  5. Create a Biml library for all of your common design patterns (Import from flat file to staging table, import from database table to staging table, populate a fact table, populate a type 1 SCD, populate a type 2 SCD, etc.)

If you made it through step 5, you’re probably ready to jump into Biml Script. Try creating BimlScript that copies the data from a specified database to another location (see this blog post by Bill Fellows for help). This will come in handy any time you need to refresh data in a dev/test environment by copying it from production (or another environment).

From there the SSIS world is your oyster, and I’m sure you will see many great uses where you can apply BIML to increase your productivity and reduce time spent on manual repetitive tasks.

The Benefits of Biml

I was able to realize the benefits of BIiml on the very first project in which I used it, and I hadn’t gotten into BimlScript at that point at all. I was the architect on a data warehouse project, working with a developer who didn’t have strong SSIS skills. But my time was split across multiple projects and I didn’t have time to coach him through every package or review every bit of his work. This happened to be for a client with whom we were working on multiple projects. And once the data mart was in production, another team would be responsible for providing support (responding to SSIS package execution failures, performing small enhancements) for it and the other data marts belonging to that client. It was important to me that all the data marts and associated ETL packages follow the same design patterns to ensure consistency and quality of work in addition to making them easier to support. As usual, we had tight deadlines and needed a way to ensure we met them despite me being stretched across multiple projects and the developer being relatively new to SSIS.

Since I had just finished building my Biml library I decided to put it to use. I added the Biml files to source control, created and published the database project for my data mart, and created the project parameters and project-level connections.  I used my Biml to create one staging package (all of my source systems for this project were SQL Server).  Then I provided a list of staging packages to my developer that looked something like this (example values changed to protect the innocent…and the guilty):

Staging Table Source System Source Table Filters or Other Logic
StageSalesCustomer AdventureWorksOLTP Sales.Customer  Straight pull – select all fields, no filters

I then sent over instructions on what to change in the Biml and showed the developer how to generate a package with BIDS Helper. The instructions were really just a list that corresponded with the comments I included in the Biml files. Ex:

  • Line 10: Package Name
  • Line 20: Schema Name Variable
  • Line 21: Table Name Variable
  • Line 41: Data Flow Name
  • Line 43: OLEDB Source Name
  • Line 43: OLEDB Connection Name
  • Line 45: Direct Input Source Query
  • Line 64: Output Path Name
  • Line 68: External Table Output Table

The developer would change the text in the Biml file at the specified locations and then generate the package. Once we finished the staging packages, I did something similar for the dimensions and then the facts. With this process, I didn’t have to worry about the developer creating the package by hand or copying another package and missing a setting or running into issues with corrupted metadata. I was confident that my design patterns were being followed. The packages were created quickly, giving us a bit more cushion with our deadlines. And I think both the developer and I came out of the project with a better understanding of SSIS and a greater appreciation for Biml. Looking back on it now, I know there was a better way to use Biml and BIMLScript to create the SSIS packages for that project. But I was happy with my first attempt at using BIML on a real project, and I used the lessons I learned to create a revised plan to use Biml on my next projects.

For More Information

I intend to write more technical posts about Biml in the future, but for now I’m perfectly happy to provide the sources of my knowledge.

 

Happy Bimling!