BIML, you either love it or hate it - either you use it to totally generate hundreds of packages or a bucket load of data flow components all at the push of a button or you are perfectly happy using the SSIS editor, you can see how the package is made up and drag the little lines from one task to another and it is simple right, why bother using xml that is more complicated, you only need to create a single package with a few tasks on it, maybe a script component here or there.

The typical use case for BIML is to generate lots of things that do the same thing, so creating an ETL process that copies X number of tables and does the same thing to each of them. This is a really obvious use case and if you have a lot of things to do which are either the same or similar then use BIML, the time you spend getting up to speed with BIML and the overhead of creating the package will pay you back significantly.

The benefits of BIML for me are pretty straightforward, I write code once and read and modify it lots of times - if I truly have a simple package that will only ever be written once, reviewed once and never modified then there is not a lot of point of benefit of using BIML. So if we have a package that will be modified at a later date either by me or someone else it is better in BIML because it is easier to maintain the packages.

OK so I will say it, I know what you are thinking "Why Edward what a wild baseless statement you make there", well true so let’s look at an example:

Example Package

The task of this example package is to have a single data flow which copies data from a table in one database to another table in a separate database on a different server, I have added a derived column in the middle of the flow, pretty standard stuff.

Changing a column datatype

We want to change a column from a uniqueidentifier to an int, the dba's have been working their little socks off and have replaced all uniqueidentifier primary keys with integers, and rightly so but this causes us an issue with our package as even though the source and destination tables have been modified, when we look at our package we get this error:

So to fix this we:

1 - Open the data flow component.

2 - Find all the tasks that have errors or warnings, open every single one in the correct order, if we are lucky then we can just open and accept the option to reset, if we are not luck then we will have to change something, in the screen shot above there is a derived column that relies on the column that has changed so we would need to use the advanced editor.

If you have one column to change then it isn't that bad, but what if you have lots of columns to change or also lots of components to change the values in, it quickly becomes a little dull having to go around and find all the components that use this column and I realise you get error validation so when you change a column, anything that depends on it will turn red but it is easy to see how even small changes can amount to lots of work needing to be carried out.

So how does BIML help?

Well if you need to change a column, because the types are generally inferred dynamically it is simply a case of rebuilding the SSIS package from the BIML script, so in BIDS Helper, right click the BIML file and choose to generate package - now that is pretty simple and even if you make a mistake and right click the wrong package, so what? It is hard to make mistakes and any system that makes it hard for developers to make mistakes is a good system in my eyes.

If there are more complicated things to do like change the types of columns in derived columns, it is still pretty simple - you have it all spelt out in front of you and can change it quickly and rebuild the package.

So making simple changes are probably easier, big whoop

Tuff crowd.

Ok so some stuff in SSIS seems like it should be simple, moving tasks around for instance but using the ui has always felt a little frustrating and I am sure quite a lot of time is spent trying to make those blinking (they don't blink, they are annoying) lines, line up - instead just copy and paste the BIML form one part of your script to another and the task is moved - it is small things like this which make it much more pleasurable to use - when you don't have to wait 30 seconds to paste a task, when it happens instantly - that feels good.

That is annoying your right, i'm sold

Easy crowd!

So with SSIS you have lots of tasks and it is like one big GUI app, you get the main package and then each of your individual data components, all the tasks and components have properties and some of these appear on the properties window and some appear when you double click the tasks but the worst thing is script tasks, these things are ridiculous - whenever you want to see what a script does you need to open a whole other instance of visual studio - even on the fastest machines this is slow, then of course you want to see what variables are being passed in so you need to close visual studio and then go back into it and oh you didn't compile the project - uh ho!

In BIML you have a script block, which admittedly is a little ugly but you can use the visual studio xml collapsing to hide them when you don't want them, expanding one of those things takes like a millisecond compared to x minutes in SSIS.

Having everything in one place makes it really easy to discover what a package does and if you really miss the "blinking" lines generate the package and take a look.

It doesn't all smell of roses

So I like using BIML because of the benefits it gives me but it is important to point out that it isn't perfect, the UI is lacking - specifically if you use C# in your biml script to generate the package you gets lots of errors in the ui that aren't there in real life and the intellisense support is poor. Don't get me wrong it is considerably better than just using SSIS but it isn't a completely polished thing, using “Mist” the enterprise version maybe better so I would encourage you to use that if you can.

For me I would like to see a really simple fully supported API, there is the Microsoft Managed DTS Api which lets you create packages but it is quite difficult to do things, there is also the EZApi which doesn’t look like it has been updated in a while but did look really promising. I hope one day that if SSIS is the ETL tool for Sql Server then there is a better way to create packages and that isn’t necessarily XML.

The other thing is that it is a new thing, to get started you will need to learn it which can be daunting and you might well be able to write a package quicker in SSIS, but I guarantee you that in 6 months’ time when you need to see what the package does or change it you will really see the benefits of BIML.