No Excel 2007 Table Support in SSIS?

When I connect to an Excel 2007 workbook using the new Microsoft.ACE.Oledb.12.0 provider in SSIS 2005, I notice that any tables that I've created in the worksheet are not recognized in SSIS.

In the OLE DB data source component (using the "Table or View" data access mode) the any table(s) I've created are nowhere to be found in the drop down list. Similarly, when I constuct a simple SQL query on the workbook, such as "SELECT * From MyTable" it returns the error:

Microsoft Office Access Database Engine

Hresult: 0x80004005

Description: "The Microsoft Access database engine could not find the object 'MyTable'. Make sure the object exists and that you spell its name and the path name correctly."

I know I have the name right -- I can use structured references to my named table in the worksheet without any problems.

Does the new ACE provider not support Excel 2007 tables? Am I stuck with using "overlapping" cell references to capture data from tables in my worksheet?

I see that you are using Named Ranges. What if you use the worksheet name?

The Office 12 versions of the Jet Provider and Excel Driver are rewritten, so it's possible that behavior has changed, whether intentionally or unintentionally. Previously the driver displayed both sheets and named ranges as "tables".

-Doug

|||

Apologies for not making myself clearer. I'm referring to the improved table objects in Excel 2007, discussed here:

http://blogs.msdn.com/excel/archive/2005/10/25/484915.aspx

When extracting data from an Excel 2007 source, I'm able to access all of the information on the sheet or from my named ranges without any problem -- both appear as what SSIS called "tables" in the OLE DB Source dialog box. This behavior is the exact same as it was using the JET provider.

My problem is that the Excel 2007 table objects that I've created in various sheets simply aren't accessible -- they don't show up at all. Given their structured, unifiorm nature, one would expect an Excel table to be recognized by the new ACE provider just like, say, a named range.

Why named tables instead of simple named ranges?

-- Tables can grow dynamically as you add rows, and named ranges cannot. My thinking was that if you could import an Excel 2007 table, you wouldn't have to worry if the named range was large enough.

-- Excel 2007 tables can be filtered and sorted, giving the user in the workbook a richer experience.

I think it would be very powerful and useful to access data as it is represented in an Excel 2007 table object through SSIS. I haven't been able to determine if this is even possible, or if I need to take some special steps to make it work.

|||

The driver has no knowledge of any new features in Excel 2007. This is merely the legacy driver, updated to connect successfully to Excel 2007 files, which as you know use a different file format.

-Doug

|||

Thanks for your quick reply -- looks like I'll have to go back to named references.

Also, for anyone wanting to do a similar thing with "Lists" in Excel 2003 (the precursor to Excel 2007) a quick test seems to indicate that the Excel driver (for pre-2007 versions) does not support them, either.