Creating a Meta Data Driven SSIS Solution with Biml

Biml

Biml (Business Intelligence Markup Language) is a markup language that enables you to quickly represent a variety of database related models and constructs, including SSIS packages, models, permissions and more.

In simple words, Biml can be used to create metadata driven solutions.

Biml is a way of describing your BI  solution in a simple xml format.

BimlScript extends the language by enabling authors to embed c# or vb.Net code with Biml, similar to how ASP.Net includes .NET code with static HTML markup.
Biml authored BI assets can currently be used with the BIDS Helper.(http://bidshelper.codeplex.com/)

Screenshot: Biml Implementation

 **Click to enlarge the image

Biml and BimlScript address these disadvantages:

• The Business Intelligence Development Studio (BIDS) and SQL Server Data Tools (SSDT) emphasis on graphical tools, hence the dtsx file format was not designed to be readable or writable. The dtsx file includes a lot of information like surface co-ordinates, GUIDs and other designer specific markup. For example, changing the position of a task on the Control Flow results in a change just like rewriting the contents of a Script Task. Changes to code are mixed with changes to non-code.
• Change tracking and merging is not possible.
• Package edits can only be done one at a time and edits cannot be applied to a set of packages.
• Refactoring many packages  is costly, time-consuming and error prone.
• Limited use of implementing many aspects of agile development methodologies such as continuous integration, code reviews, incremental development, and code refactoring.
• The focus on the generalist has implications for abstraction model. Inherently, there are very limited capabilities for abstraction and reuse. There are no inherent capabilities for formalizing patterns and practices.

Biml and BimlScript Features:

• Allows you to create a template which can be used to create similar SSIS packages and hence reduce the effort
• Increased Quality: Since SSIS packages are generated from the same template, the quality of the code increases. Increased quality further translates into reduced testing requirements.
• Biml is designed to work with Agile development and management practices. Biml works great with source control, merging and file compare tools.
• Biml generates compiled code, and all the compiled code is usable in the standard Microsoft tool set. Biml is easy to read, even by people new to SQL Server