I have recently started using Biml & Mist. Both of these products have been created and maintained by Varigence. Most experienced SSIS / BI developers that I’ve had the pleasure to work with have expressed irritation with how much time is spent recoding a familiar pattern, but for a different source system or a different set of tables, etc…
This is where BIML comes into the picture. Biml is a domain-specific XML dialect for defining business intelligence (BI) assets. On it’s own Biml allows you to declare BI assets without worrying about a lot of rubbish that is normally save with an SSIS package (e.g. the layout information). This has the added benefit of not having to deal with the quirks of the SSIS development environment (of which there are many). With the addition of BimlScript though, BIML becomes a very powerful SSIS generation platform. BimlScript adds the ability to embed C# or VB.Net into your BIML files. “BimlScript is used to merge changes into existing Biml assets, automatically generate Biml code, and apply changes across multiple Biml files during compilation”.
There are already plenty of blogs out there explaining what Biml is, so I won’t regurgitate that. What I’d like to talk about is my experiences learning Biml/Mist and how this has changed the way I view SSIS development.
My first project
For my first project, I luckily had a new project starting with an existing customer where there is a lot of mutual trust. This project was a new ETL process into a brand new Data Warehouse. The biggest challenge for the ETL development is that the source system was still under development and as such would have extensive and rapid change. They wanted us to start ETL development in the mean time, which meant that there would be a lot of revisiting existing ETL code to make some minor changes. I explained the potential benefits of using Biml to my customer and they agreed that in their case it was worth the risk of letting me implement a new (and to them untested) method of development.
Firstly, like any new language, there was a significant learning curve to start with. That is to be expected when learning anything new. Probably my strongest criticism of Biml & Mist is that it is currently a bit light on good training content. There are plenty of samples out there, but you basically need to figure it out for yourself. Having said that, as I got a grasp on the basics, it started getting quite easy. I will say that to be effective, you will need a decent grasp on SSIS & .Net . Luckily I have these so it was right up my alley. The production of good training material will hopefully be high on the agenda of someone in the community (or at Varigence).
To use Biml & BimlScript you are basically going back to basics. It feels like you are developing in a text editor. I don’t mind that, but I know many who don’t like it. In BIDSHelper, the intellisense and the overall experience is not ideal. From what I understand, all of these issues are due to limitations in Visual Studio. So using Mist was a breath of fresh air after first doing the development straight in BIDSHelper. James Beresford recently blogged about the Mist interface here. He was not very complimentary, but he did have several valid points. If Mist is going to be every Biml developer’s tool of choice it will need some improvements.
So given this was my first project using Biml/Mist, I am sure there are still a host of tips and tricks that I have to learn. However I have been very pleasantly surprised. I developed my own (rudimentary) meta-data layer to sit over Biml, and using BimlScript and Mist’s transformers I have built a framework whereby I no longer fear change in the source systems. My framework has a long way to go before it is perfect, but the real beauty is that you only have to solve a problem once and then it just works in all the applicable situations. I would now struggle to go back to the traditional way of SSIS development for anything but the smallest or most unusual projects.