This statement about the ACE Provider and its Excel driver caught my attention on an internal discussion list at Microsoft on 12/26/2010:
"The current support position from the Office team responsible for the ACE provider (both 32-bit and 64-bit) is that it is unsupported to use the ACE provider on any server-based system. Over here in the XYZ team, we have gone around and around with them and they are very clear. What you are trying to accomplish, regardless of the ‘bit-ness’, is technically unsupported, and while it might ‘work’ that does not imply ‘support’. If you ran into problems with the ACE provider, CSS will not be able to get hotfixes, QFEs and other changes implemented."
Does this statement change anything? Not really. Obviously your SSIS packages are not going to stop working if they're working today.
Should this position surprise us? No. Jet is a desktop database, and the drivers that are shipped with it - like the Excel driver - have traditionally been referred to as the "desktop database drivers" in Jet documentation.
What's the concern with server-side execution? I don't know. It's not as if a database driver is at risk of popping up its UI while no one's watching. I suspect that this is more an indication of the half-hearted support for Jet.
What's the long-term solution for an Excel provider for SSIS? From Microsoft, I would be nervous. (See also: Tablets to compete with iPad.) Seems to me there's some money to be made here in the custom component market, however!
A custom Excel Destination adapter would be easier to create than the Source - you simply have to write out the data from the data flow using Office Open XML. Here's a simple example of writing out data from a database using Open XML: http://blogs.msdn.com/b/brian_jones/archive/2008/11/04/document-assembly-solution-for-spreadsheetml.aspx.
A custom Excel Source adapter would face the same challenges that frustrate users today...
- What data type shall I use for a column that has no metadata? OK, I have to guess.
- How do I guess intelligently? OK, I'll sample some rows.
- Whoa, there's strings AND numbers in the column! Which data type shall I use? OK, I'll use the majority data type. So let's call this column numeric.
- Whoa, what do I do now with the string values in some of the cells? OK, I can't cast them, so I'll replace them with nulls.
...and so forth.
Will you be the hero who writes the next-generation Excel Source and Destination adapters? It's a sad irony that support for Excel as a data source has been dying slowly at Microsoft for years, while the importance of Excel in Microsoft business intelligence is still growing.
Reminder: Comments on my blog are disabled, but you can send me email.
Thank you for following me!