Wednesday, June 18, 2008

SSIS Excels - But Not at Excel

In the two months since I began using Google Analytics to track visitors to this blog, I've noticed a few things:

  • Two of the top three articles on the blog are related to working with Excel.
  • 26 of the top 100 searches that referred users to the blog had to do with Excel in one way or another.
  • SSIS doesn't deal with Excel data particularly well.

Ok, I'd actually noticed that last one long before I started using Google Analytics. And you've probably noticed it too.

I've long considered posting more about using SSIS and Excel, but I haven't, primarily because it's not something I deal with regularly in the real world, and because it's too annoying an exercise for me to go wading into it when I don't really have to.

And yes, I suppose that's another way of saying that I don't really love you enough to do the work.

But fortunately, Douglas Laudenschlager[1] does love you - he loves you more than you know. How do I know this? Because he is writing a three-part series of blog posts about how to work with Excel data in SSIS. Check this out:

  1. Connections and components
  2. Tables and data types
  3. Issues and alternatives

These articles present great information about how Excel manages data, what connectivity options are available and how to effectively use those options from within SSIS. I honestly don't know if there is anything in them that is not available elsewhere, but I can say with certainty that there is no other single location that covers all of the information that Douglas has gathered into these posts.

Please do yourself a favor and read Douglas' posts. If you deal with Excel data in your SSIS packages, you owe it to yourself. I know that the next time I need to wrestle with Excel in SSIS, I'm going to re-read all three posts before I spin up Visual Studio...

UPDATE: As of 21 June 2008, Douglas has posted the third and final installment in this series, and the link has been added above. I love the fact that he starts off with "The Root of All Evil." That's Excel in a nutshell...


[1] In case you haven't been paying attention, Douglas is a technical writer on the SSIS documentation team. He started blogging back in late April 2008, but has been consistently posting such excellent technical content that his blog has been on my "must read" list ever since.

No comments: