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.


About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in BI, SSIS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s