Stairway to Biml Level 2: Biml Basics

  • Comments posted to this topic are about the item Stairway to Biml Level 2: Biml Basics

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • Hi,

    Is it possible to do it the other way around too?

    I am pretty sure that the the metadata and the pkg setup is stored into an xml file by default by SSIS, but can SSIS be forced to write into this BIML file as well?


  • sergiu0303 (7/31/2013)


    Is it possible to do it the other way around too?

    I am pretty sure that the the metadata and the pkg setup is stored into an xml file by default by SSIS, but can SSIS be forced to write into this BIML file as well?


    I believe that Mist has this capability, but I don't think that it is possible to accomplish using BidsHelper.

  • Being able to import existing packages to create BIML is something that Mist does. But if you're just looking to use SSIS to automatically generate some metadata in Biml, you can do that with BidsHelper (or excel, or a variety of other tools). Being a human-editable language, there are a ton of options. Do you have something specific in mind? I'm sure I could recommend a pattern I've seen used successfully before.

  • Thanks for posting this. I am really pleased to see that this exists, and it sounds like BIML may fill the hole with SSIS that has frustrated me for many years. Thanks.

  • Agreed! I'm very excited to start using BIML. One of the complaints I've always had about BI (SSRS/SSIS, etc...) is that beyond the SQL code, it's seemed like it was basically presumed that I would rather use a GUI than code things myself, when in fact, for me it's precisely the opposite. I hate GUIs (and the nasty, unmanageable code they tend to generate). I like to have complete control of the code in my projects, and be able to account for every keystroke, what it does, and why it's there. NO GARBAGE CODE PLEASE! 😀

    Whenever I inherit code from other developers in my company and it's a sloppy, unreadable mess, I just shake my head and weep for the future of software development... and then I clean up the mess 😛 I feel like I'm the only one who cares about clean code (and it's reusability)! So frustrating... I would much rather code my own Reports/SSIS packages, etc... but the XML code generated and used in these RDL/DTSX files is not meant for use in day-to-day coding (all the GUIDs, etc...). However, I have edited the code manually for existing RDL files just avoid openning the GUI and dealing with the overhead there when I just need to make a few minor tweaks 😉

    Maybe I'm just not "hard-core" enough to code my own RDL/DTSX files from scratch (more like I think that seems like a huge waste of time)... For HTML/JavaScript/.NET coding though, I only use code view, because those languages just seem like actual programming languages to me (without all the computer code gobbledy-gook, if I'm gonna mess with that, why don't I just learn to code in binary while I'm at it... :doze:).

    BIML on the other hand seems like it's going to be a real programming language akin to HTML, etc... A precisely controllable, precisely reproducible and therefore reusable, programming language that is reasonably readable and more importantly writable by people who know how to read/write programming languages.

    I hope this just continues to grow in popularity, and eventually, instead of having to compile the BIML code to generate the DTSX, SQL Server will just be able to execute BIML code :w00t: Also, I'd like to see BIML (or some other mark-up language, but why not just extend BIML since it's all BI) generating RDL files as well for the front-end element of BI. If this already exists, and I'm just clueless, feel free to gimme a clue :hehe: This was my first time hearing about BIML, so it wouldn't suprise me if there's more new goodies for me to learn about.

  • Hi pjcrout,

    I'm Scott Currie, the creator of Biml. We're definitely on the same page, as far as the motivation and capabilities. Biml is a full-fledged programming language. Presently we have relational databases, SSIS, and SSAS modeled in Biml. We also have an IDE made just for Biml that has visual designers, powerful code editors, live scripting hosts, and more. It's called Mist.

    What we decided to do was make the SSIS subset of the language entirely free through BIDSHelper, and make relational and SSAS modeling available only through the paid product. This was both because the BIDS approach to relational and cubes doesn't lend itself well to Biml-style automation, and because we need to have some value-adds to keep the doors open through license revenue. There are a couple person decades of dev time in the Biml/Mist system with a lot of additional possibilities for future development, so it is something that needs ongoing revenue to support. We don't presently support SSRS with Biml, but we will likely be adding that in the first half of next calendar year.

    If you like what you see of Biml so far, also check out transformers ( - which allow you to modify existing Biml objects. This lets you build frameworks that modify the code your developers write - automatically apply logging patterns, enforce coding standards, you name it. Transformers are also a Mist-only feature, but if you invest in them, they are insanely powerful. In fact, we use transformers internally within BimlEngine to implement much of the compiler functionality. And with Mist, we allow you to override even our default functionality with your own transformers if you like.

    For example, you could write all of your code using a Lookup component, but add a transformer so that all those lookups automatically turn into an Inferred Member / Late Arriving Lookup pattern when you build. If you ever need to change the pattern, just change the transformer file in one location and rebuild. You can even have a debug and a production set of transformers, so that in debug mode you insert extra instrumentation code. Or if you have a heterogeneous server environment, you can have different transformers for building against SSIS 2005 vs SSIS 2008 vs SSIS 2012. We'll have some very exciting examples of transformers published over the next several weeks, so please keep your eyes open for those.

    If you have any other questions, please ask away.

  • Sounds great! Given that I find positive results working with BIDSHelper, I will likely be having some conversations around getting licenses for Mist in the near to mid-term for our company (fingers crossed) 😉

  • There are 3 biml script types.




    Does BIDS Helper allow for the creation of these?

    Can BIDS Helper be used to create BIML that modifies existing packages?

  • Hi Kevin,

    BIDSHelper only supports executable Biml files.


    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • I don't expect Mist sells very well in German-speaking countries! 😉

  • Hi.

    There is a mistake in post.

    "Figure 18 – Setting the Project ProtectionLevel " - asked choose ProtectionLevel property "EncryptSensitiveWithUserKey", but in "Listing 5 – Biml After Adding Package ProtectionLevel Metadata" - ProtectionLevel property set to "EncryptSensitiveWithPassword"

  • Thanks for the interesting article.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply