Now that we know our Biml basics, we'd like to start automating the generation of Biml code. Before we do that, though, let's pause for a moment to reflect on the benefits we've already accrued. With Biml code, we now have human readable/writable source code with at least a few powerful benefits:
- It works great with source control.
- It is fully integrated and consistent across relational, SSIS, and SSAS.
- You can easily use find/replace mechanisms to manage the code.
- Code reviews and other management processes are much easier
For some projects, just those benefits would be worth using Biml over the standard BIDS and SSDT tools. Once we start automating, the benefits increase exponentially.
Copy/Paste and Editor Macros
The first path toward automation that many Biml developers will take is to use features built into their favorite source code editors. This doesn't really qualify as full automation, but it's done commonly enough that it deserves a mention.
If you create a library of commonly used snippets, those snippets can be very quickly added to your project in the appropriate places either through simple copy/paste or through a snippet/macro capability built into your favorite editor. This approach to automation has many downsides, the most obvious of which is duplicated code that can become a maintenance headache - but again, it is commonly used and for simple scenarios might be a reasonable choice.
Furthermore, with Biml your snippets will be much simpler than you might expect. Since Biml uses a design philosophy of convention over configuration, most common values do not need to be specified. This leads to shorter snippets and fewer false positives during find/replace operations.
The next most common approach to Biml automation is through excel. This makes quite a bit of sense once you realize that a tremendous amount of metadata already exists in Excel workbooks in many organizations.
Do you have a data dictionary in Excel? If so, you have a metadata source that you could use to generate the Biml code corresponding to that data dictionary or even packages to load those tables.
The way this normally works is that an extra column or worksheet will be added to the data dictionary (or other metadata) workbook. This column or worksheet will use string concatenation in Excel to dynamically create Biml code representing the desired constructs. For example, you could use an Excel function similar to the following to create the column elements for a data converstion task:
=CONCATENATE("<Column SourceColumn=""", A1, """ TargetColumn=""Ansi", A1, " DataType=""AnsiString"" Length=""50"" CodePage=""1252"" />")
Of course, it's difficult to provide a definitive guide to the best way to handle Excel-based code generation, because the approaches you might take will vary considerably with the format of the spreadsheet, the type of metadata you have captured in that spreadsheet, and the desired Biml output.
Some developers have found tremendous success with Powershell and prefer to use it as their general purpose automation tool wherever possible. This includes the use of Powershell to automatically generate Biml code. While this is not the recommended way to go about automating Biml (any scenario that required Powershell will usually be better handled by BimlScript which you will learn about below), it is supported and can make sense in environments where Powershell is heavily leveraged.
For more information about how to use Powershell to automate Biml, I strongly recommend Allen White's talk on the subject: https://www.youtube.com/watch?v=rlrw0dNmFdU
Other development teams are very skilled with SQL coding and prefer to keep as much automation code within SQL as possible. For those teams, automatically generating Biml with SQL scripts is a reasonable option. As with Powershell, we normally recommend that you use BimlScript instead, but SQL works and works well.
To create Biml code with SQL, the most common approaches are to use:
- String concatenation
- XML functions built-in to the SQL language
- XML functions built with using .NET
The choice of approach depends primarily on personal preference and skill set.
One of the other benefits of using SQL scripting to work with Biml is that the built-in XML data type can be used to store Biml in your database. Furthermore, the built-in XML functions can be used to parse and search, in addition to modifying the Biml XML. For more information, refer to the SQL Server XML function documentation.
Due to the popularity and usefulness of XML, a variety of tools have been created to assist with the creation, processing and manipulation of XML. Perhaps the most commonly used of these is XSLT which is a language and collection processing tools that can transform XML documents. Especially for those who have existing metadata in XML documents, XSLT and other XML tools can be used to transform that XML into the corresponding Biml for your application. This is not a recommended approach for the average Biml user, but for those organizations with considerable experience in XML processing, it can be a good choice.
There are actually two ways for Biml code to be automatically generated by .NET programs.
The second is more interesting. In addition to providing an XML-based language, Biml also provides a robust API. In addition to using this API from within your BimlScripts, you can also use it to create a complete Biml object model. Once you have created the Biml object model, you can then retrieve the corresponding Biml code by using the GetBiml method provided by the Biml API. We'll learn more about how to use the Biml API in later lessons.
By far, the best Biml automation choice for most developers is BimlScript. Building on ideas from technologies such as ASP.NET, PHP, and Ruby on Rails, BimlScript enables you to embed small nuggets of C# or VB code into your Biml XML. These code nuggets can be used to create variables, call .NET APIs, access metadata, create loops, implement conditional logic, or do basically anything else you could do with a full .NET program.
The benefit of BimlScript over a .NET program is that the BimlScript does not require you to specially handle XML creation. Just write the XML as you normally would and embed the relatively small amount of coding logic into nuggets. You'll find that a little .NET code goes a long way with BimlScript.
Clearly there are a variety of options for Biml automation. While you should always use the option that is the best fit for your project and team, BimlScript is the recommended option for most scenarios. Most of the remainder of the lessons in this course will focus on how to use BimlScript.
Complete the lesson Techniques for Automating Biml Code: