No. No, you do not need a Biml Framework.
In the same way that I do not need a vehicle powered by an internal combustion engine, you do not need a Biml framework.
You see, I live about 5 miles from Farmville Virginia. I could walk to town each time my family needs groceries. I could pack as many groceries as I can carry into a backpack and walk the 5 miles back home. There are several benefits to this, not the least of which is the exercise that I would get walking to the store and then walking back – carrying a load of groceries, even!
“Is walking to the grocery store efficient, Andy?” Well, if you’re going get all persnickety about it… I suppose not. But think of all the money I would save on vehicle maintenance and fuel – plus the benefits of exercise!
Does this reasoning sound silly to you? It does to me (and I wrote it). Let’s add a dose of reality, shall we?
The Art of Data Integration Architecture, Part 1
Like many sciences, data integration is part art and part science. The art part is just good judgment. My lovely bride, Christy, has an awesome saying about good judgment: “Good judgment comes from experience, and experience comes from bad judgment.” With that in mind, I share the following in the hope that it saves you some… experience:
Don’t Use Biml…
If you are building a data integration solution that loads data from one source to one destination, do not use a Biml Framework. Heck, don’t even use Biml for this. Build this solution manually in SSIS or T-SQL or some combination thereof. It’s quicker, easier, and less-frustrating to construct a loader manually.
If you are building a data integration solution that loads data from a couple dozen sources to a collection of destination tables, use Biml but do not use a Biml Framework. It takes about 40 hours to become proficient in Biml. To learn Biml (or anything, really), you first need a real-world problem to solve. Next, you need the gumption to try and solve that problem using a tool you have not yet used to solve a real-world problem.
Why wait until you need to build a couple dozen SSIS packages? It takes me about 2 hours to build and test an SSIS package that incrementally loads data. The math works:
24 x 2 = 48
If I take the time to learn Biml, which will take about 40 hours, I can complete two dozen loaders in less time than it would have taken me to build those SSIS packages manually. Plus I’ve learned something. More on this in a bit…
Use a Biml Framework…
After you’ve invested the initial 40 hours in learning Biml… If you are building a data integration solution that loads data from one or more sources to a collection of destination tables, use a Biml Framework. Once your framework is built, you can decide when it makes sense to use your automation and when it makes sense to build loaders manually.
I wrote about a Biml Framework here.
You can download an early example of a Biml Framework here.
Perhaps you’re looking at all the innovation and automation happening around you and thinking, “This is just some passing fad. The djin will be put back in the bottle soon and we’ll return to the days of carving our own integrated circuit chips out of wood.” Ok, maybe you’re not thinking precisely that. But I hope and pray you get my drift.
If the announcements of the latest Microsoft conference have taught you anything, they should have taught you that automation is following the arrow of time. Automation is on the increase in both volume and complexity. One benefit of having lived over half a century (and doing technology for most of that time) is perspective:
I promise you, the djin no longer fits in the bottle.
If I’m scaring you, good.
I’m not arguing the merits of automation.
I am arguing the facts of automation.
I am not attempting to assess the potential good – or potential harm – of automation. I am stating that it’s here to stay – and grow.
If you want to live and work in a field where you can master a craft and simply do the same thing until you retire, technology is not the field for you. Technology is the field for you if you enjoy learning and growing.
“How much time should I spend learning new stuff, Andy?” Experience informs me the answer is “about 25% of your time should be spent learning and growing.”
My brother and friend Kevin Hazzard (DevJourney | LinkedIn | (awesome and fascinating) DataDriven interview) refers to “numbers” as “nummers.” There are a nummer of reasons I like this mispronunciation… perhaps I will explain another time. I bring up nummers here because – as my friend and brother Aaron Lowe used to tell me (before he passed away earlier in 2018 – I miss him…):
Math is hard.
I once delivered a solution that contained several hundred SSIS packages using Biml. The unit-of-measure for my estimate, based on the nummers I shared earlier (2 hours per package based on an incremental load SSIS design pattern) was “months.”
I delivered the solution in days using Biml.
It’s possible your employer isn’t good at math.
It’s possible your employer is more interested in providing you a job than in making a profit by remaining competitive (I love it when politicians speak of providing jobs… don’t get me started…).
The nummers argue that one day things
may will change for your employer. If When that day arrives, things may will also change for you.
(Did I mention that I hope I am scaring you?)
At the time of this writing, it is not possible for others to take your knowledge away from you. I pray that day never comes, but more than half-century of existence has trained me to not by surprised by anything.
Learn as much as you can. Consider knowledge a hedge against future changes. Add knowledge of the new and shiny to your experience. I don’t care how old or how young you are, start today.
I share this in the same spirit of you wanting me to get more exercise by walking to town to buy groceries, except that I am right about this and you were… less efficient. 😉
Start at SQL Server Central. It’s free.
Well, you have to give them your email address.
I understand that bothers some folks, but I encourage you to carefully weigh the benefits of sharing your email address with an entity that provides this much free education against falling behind in technology trends, application, and education. It’s totally your call, but you may find yourself in possession of the cleanest Inbox Zero of anyone who is uninformed and increasingly less-employable…
The Stairways are awesome.
Head over to edX. Many courses there are free. If you want, you can pay for certificates that you can post on LinkedIn. But – please – go get the knowledge.
If you want to dive deeper, sign up for Biml Academy or SSIS Academy or Fundamentals of Azure Data Factory! I’m still building the material so the costs for subscribing to the academies are low.
One thought on ““Do I Need a Biml Framework, Andy?””
Thanks for the food for thought (especially with the funny and smart comments, I really liked your bride’s saying!) as I have come across to BIML 3-4 times in the recent years and I always wanted to start learning it but then life got in the way (it often happens and I know it’s mostly the lack of perseverance to make it happen…). I still have it on my list but recently I have had some exposure to Azure Data Factory and I keep trying to find out what the future of SSIS is and if it’s worthwhile to invest time and effort in learning BIML.
I think one possible future roadmap of MS for SQL Server ETL is ditching SSIS in a few years time and ADF will take over completely. Of course the situation might pan out less extreme but that still makes me wondering how long SSIS will be around.
I’d enjoy reading your opinion about it!