SSIS with Microsoft Office (2007)
SSIS uses two different OleDB providers to connect to MS Office data sources such as MS Excel files and MS Access databases: The JET Provider and the new ACE provider. JET is used to access all Office versions up to 2007, and ACE is used for Office 2007.
Bob Beauchemin provided us with a nice Excel12 sample package that uses the new ACE OleDB provider to connect to Excel12 files.
MS Office (versions up to Office 2007)
JET stands for Joint Engine Technology, sometimes being referred to as Microsoft JET Engine or simply Jet. Microsoft Access and Visual Basic use or have used Jet as their underlying database engine. It has since been superseded, however, first by Microsoft Desktop Engine (MSDE), then later by SQL Server 2005 Express Edition and no longer exists as a component of Microsoft Data Access Components (MDAC). For larger database needs, Jet databases can be upgraded (or in Microsoft parlance, "up-sized") to Microsoft's flagship database product, SQL Server 2005. However, JET is now part of the Windows platform and comes with the default installation of all windows flavors.
JET is used for connectivity to Excel and Access files in SSIS, up to Office 2007 version. In SSIS, there is a special Excel Connection manager which is essentially a wrapper around JET, and SSIS blocks the JET OleDB provider within the existing list of OleDB provider choices.
Office 2007 & the ACE provider
Microsoft Office 2007 files have a new format and therefore are not supported by the Jet 4.0 OLE DB provider. Specifically, the Microsoft Excel data source and data destination, which uses the underlying Excel connection manager based on JET, cannot be used with Microsoft Excel 2007 files. Microsoft Office 2007 shipped with a new OLE DB provider, the Office 12 Microsoft Access Engine OLE DB Provider (aka ACE provider). The ACE provider must be used with Microsoft Excel 2007 and Microsoft Access 2007 data files, but ACE supports only Microsoft Office 2007 data. SSIS has not been specifically tested with other data sources that are compatible with ACE, such as Microsoft SharePoint files, though it is possible to use them with ACE.
To be able to use the ACE provider, one has to pick the ACE OleDB provider, just like any other OleDB provider in SSIS, and not use the available Excel & Access options.
ACE provider is supported in SQL Server 2005 SP2 and later.
64-bit considerations:
Both JET and ACE providers are 32-bit only, and thus can be supported natively on 32-bit platforms and using WOW64 mode on IA-64 and x86-64. In order to use Jet and Ace providers in 64-bit platforms, 32-bit version of dtexec.exe must be invoked. For more information on connectors and 64-bit considerations, please see the article on 64-bit story.
More Background on JET vs. ACE
JET provider is a system component, developed by the SQL Server team, and was being used by the Office team. Since the SQL team is de-investing in Jet, and telling developers to code against SQL Server Express, but as it lacks the heterogeneous query functionality the Office team needs, Access got stuck with Jet.
And thus, Office team needed to extend the Jet engine, and took a “private” copy of Jet, and extended it for Office 2007. This means Access 12 no longer uses the system Jet engine, but is tightly bound to its own version.
And on the flip side of the coin, JET does not support Office 2007.
Below is an overview of some of the critical data points about the two providers:
FEATURE |
JET |
ACE |
Availability |
All Platforms (XP, Windows Server Family, and Vista) |
Office 2007 and a planned redistributable web-download |
Supported Platform Architectures(this was asked a lot) |
32-bit only |
32-bit only (yes, no 64-bit support here) |
Supported Office Versions |
Up to Office 2005 |
Up to Office 2007 |
**Note: **This article has been ported over from http://ssis.wik.is/ Note: If you were the owner of above article, please update it to the latest or with other appropriate information.