Saturday, August 25, 2007

SSIS == Fresh Peaches

Here in Upstate New York, local peaches are at the height of their season. I bought a bushel of them last night at a little family-run produce stand, and today their delightful aroma is filling my house. For breakfast this morning I made waffle cakes[1] for the kids, and for my serving I chopped up a few peaches and threw them onto the batter on the griddle, so I ended up with peach waffle cakes. Instead of topping them with maple syrup, I chopped up a few more and poured them over the top of the stack. The peaches inside the waffle cakes were hot and slightly caramelized, and the peaches on top were cool and fresh - it was a delicious combination. And while I was eating, I was flipping through one of my favorite cookbooks[2] finding more delicious ways to use these incredibly flavorful fresh peaches.

Life is good.

And, of course[3], these peaches and the vast range of amazing things that you can do with them got me to thinking of SQL Server Integration Services.

How so? Well first let me introduce you to my brother. For this discussion we'll call him Jason. Jason doesn't cook. The height of his culinary expertise is tater tots. He pours some from the bag onto a cookie sheet and puts them in the over following the instructions on the bag. When he's feeling particularly adventurous, he might put a little ketchup on the plate once they're done.

No, I'm not making this up.

For Jason, the perfect peach is the peach that comes in a can, swimming bisected in syrup with a few of its closest friends. Open can, pour into bowl, eat. What could be better, Jason thinks? All of the effort that has to go into fresh peaches, including peeling, pitting, cutting and (in the worst possible scenario) cooking as part of some larger dish, is not even worth considering. Why bother? Even if the end product is something that you could not get with canned peaches, why even consider it if you can have a bowl of canned peaches for almost zero effort?

Now you see, don't you?

In the world of ETL tools and platforms, Data Transformation Services is canned peaches. It's really incredibly easy to get started and to do very simple things. But if you want to do something truly amazing[4] you're pretty much doomed from the start. For some circumstances you simply "can't get there from here" and even if you can, the end product is never quite what you hoped it would be.

SQL Server Integration Services, on the other hand, is fresh peaches picked at the peak of freshness. Practically anything that you want to do, you can do and do well. It might take a little extra effort to do things right, but if you take your time you can make the most delicious[5] recipes imaginable.

But it's not always as simple as if you were using DTS[6] and because of that, people like my brother sometimes throw up their hands in disgust. I think this is one area where the SSIS product group needs to spend a little effort before they think about deprecating DTS entirely. As I've said many times before, I love SSIS. I really do. And I'll do just about anything[7] to avoid using DTS for anything but the most trivial tasks. But before it can be the right ETL tool for my brother, SSIS needs to be ready to get poured into a bowl, ready to eat.[8]

I'm somewhat ashamed to admit this, but I have yet to look at the Import and Export Wizard in SQL Server 2008. Nine times out of ten I'd rather build a package by hand than use a wizard to get me started, so I haven't made it a priority. But these delicious peaches which are calling my name made me think of my brother and of how the SSIS tools need to mature before we can get rid of those nasty canned peaches once and for all.

And now I have a bushel of peaches to peel and prepare, so please forgive me...

[1] Basically this is waffle batter, made with whipped egg whites to be extra fluffy, but cooked on the griddle in a pancake-like form factor, instead of being cooked in a waffle iron. If you want the recipe, just ask.
[2] The Pie and Pastry Bible by Rose Levy Beranbaum, if you must know.
[3] What? You didn't see this coming?
[4] You should see the Peaches and Cream Tart recipe I have planned!
[5] And high performing, maintainable, scalable, etc. Everything a great dessert needs to be.
[6] Canned peaches, remember?
[7] Yes, Lynn...
[8] So to speak.

No comments: