So there we were, Client site and they wanted a large load of tables brought over from a bunch of different source systems. They had Microsoft SQL Server (well Azure in the cloud <hand waving>) and so we needed to use Integration Services (SSIS to be precise – and we do like precision) to do all of the heavy lifting.
Now if you’re being good and following a design pattern approach for your SSIS packages (and I hope you are) you will want to create an individual package for each of your desired tables. For this case we’re talking a top count of about 150 tables for just one source! If you wanted to hand code all of that it would be like crossing the Sahara desert (on your knees… backwards).
So we turned to a very handy tool that utilizes BIML (Business Intelligence Mark-up Language). This was originally created as part of BIDSHelper – a codeplex add-on to Visual Studio that made life a lot easier when using Microsoft Business Intelligence Development Studio. BIDS doesn’t really deserve capitals by the way – its just a cut-down Visual Studio shell with special project template add-ins for SQL Server (but I digress).
Enough reminiscing – “what does BIML do?” you cry. Well in short its a language with associated scripting that allows us to create SSIS packages (plus Cubes and other handy BI stuff) for lots of things with little effort (ok maybe a little effort – but that’s at the start).
150 packages – pfff.., press the button and watch magic unfold before your very eyes. Well okay, in the beginning you’re more like the magicians apprentice in fantasia – but you get there in the end. So by now you must be chomping at the bit wanting to know more about this fabulous thing and how you can use to do what you need to do – like right now!
So first things first lets get our tools organised:

• Development Environment – Visual Studio
• Database engine (if desired) although if you’re just doing this for the thrill of it you could transfer between two text files.
• BimlExpress add in for Visual Studio (handy down load link right here )

So now you have your development environment set up and you’re fingers are itching with anticipation. What to do next? Well I could give you a nice little starter and all that, but hey it’s the modern age – someone else on the internet has already done a great job of it. So here’s some links for great references out there.
Stairway to BIML (http://www.sqlservercentral.com/stairway/100550/)
http://bimlscript.com/ – This one is cool – it provides a self-training sequence which rewards with points (gotta get them all)
https://www.cathrinewilhelmsen.net/biml/ – this is a lovely Norwegian lady (not Glaswegian – different adjective required there) who blogs about biml and sundry.
Any way for purposes of this blog (and those of you who don’t want to do the extra reading) I’m going to explain a few of the concepts around the Biml/Biml Script and how it relates to SSIS packages.
BIML
This mark up language behaves like most xml based languages – the simplest example I can compare it to is HTML. You use the tags and attributes to define pretty much all of the parts of a SSIS package.
BIMLScript
This scripting language acts in the same vein as ASP(etc) to HTML. Having defined how to create a package with BIML we can quickly expand it with scripting to allow the generation of multiple SSIS packages, along with the creation/configuration of tasks/connection managers etc within the packages. You can use C# or VB to do this – although I think the vast majority of the examples out there are in C#.

Well that’s all well and good you cry but how does all this relate to the 150 tables you were talking about at the start? Ok, like any long winded Billy Connolly joke we do get back to the start.

Starting with a pretty picture as I’m trying to avoid a thousand words.
First we get a collection of information about the tables and database that we want to pull our data from. This can be as simple (see following code example) or as involved as you like.
Then we determine what packages will have in common (typically connections).
Then we generate all of the desired packages and have a little party.

Not a picture person? Then the below example shows how quick and easy it can be to construct a loop based on a source database. Obviously it’s missing the bits about connections and what the package actually does – but that’s just semantics…

So that’s about it for the nice free and easy stuff – there’s obviously other stuff available from the guys at Varigence but that will require payment of funds blah blah.
Until de next de time bork,bork,bork.
Brent.
Footnote
The images in this article are blatantly copied from the wonderful presentations of Catherine Wilhelmsen (https://www.cathrinewilhelmsen.net/)
Varigence®, Biml®, BimlScript™, Mist® and Vivid® are proprietary marks of Varigence.