Dave Rodabaugh's Cube

My First In-Depth Study of SSIS

I'm a BI guy.  I've been mashing on data and building cubes for several years.  I've always tended to avoid DTS because there were performance aspects of it I didn't like. In fact, I never considered it a true enterprise-scale tool, a position which has inflamed the passions of several coworkers in the past year as we discuss SSIS.  One even said, "I could pluck a star from the sky with DTS!"  Perhaps, but you had to build your own rocket to do it.

Without a good ETL tool, my cohorts and I always tended to use the "dump and chase" method.  We'd suck the data into a SQL Server data warehouse staging area, and then write stored procedures to transform and cleanse the data.  This method was not intuitive and required prodigious T-SQL skill, but it ensured performance because we could easily tune the database with indexes, and it gave us great error handling capabilities to boot.

I'm newly employed as an application development manager for a small upstart BI shop in my hometown.  One of my responsibilties is to make technical and technological decisions.  It's a foregone conclusion that we're a Microsoft shop; I wouldn't have taken the job without such assurance because that's where my expertise lies and I believe in their framework, products, and vision for BI.  So it's SQL Server, SSIS, SSAS, and SSRS.  Since ETL seems to chew up the vast majority of an end-to-end BI project, I chose SSIS as my first learning target.  I don't need detailed expertise, but I need to be able to use it and guide others in its proper usage.

So now I'm studying this strange new offering in detail and I'm impressed.  It's intuitive and everything DTS wasn't.  What I love the most is how well it conforms to everyting else Microsoft does.  It runs as a service.  The packages are saved to dtsx, an XML implementation read by SSIS.  It's extensible by .NET.  Event handlers provide an easy way to see what's going on.  The list is much larger, of course, but I'm citing a few to illustrate that if you just pick a point in the Microsoft BI continuum and start studying, you'll learn the approach and lexicon for nearly everything else as well.

Jon Baker and I are studying for certification.  We've tackled XML first because it's arguably the area about which we are most ignorant.  At first, I thought it would be an academic curiosity, though admittedly a LARGE one.  I was completely wrong.  To say it's pervasive is like saying I breathe air about 30 times per minute. I've come to the conclusion that you can't be a SQL Server 2005 product expert without an in-depth knowledge of XML and how it's used in the SQL Server suite of products.

If you're like me, you don't like to study.  I say it like this:  "I don't like to study, but I love to know."  Of course, the former begets the latter, so I have to study despite my distaste and my present struggles have reminded me that it's not important what you start studying; it's only important that you study.  You're going to see the same things in so many places that you'll eventually assemble a coherent mental picture about SQL Server 2005.  I'm fairly jealous of Allen White and others who already have your 2005 certs, but I'm willing to put in my time.  For now, that's SSIS.  I highly recommend diving in if you're a Microsoft BI guy because you're going to need detailed knowledge of this fabulous new product.