If you want to interact with Excel 2007 file formats in SSIS 2005 don't bother with the Excel connection component. Setup your connections using Microsoft Office 12.0 Access OLE DB Provider and set extended properties to Excel 12.0....
|
While you can use an Excel connection manager to connect to a data source that uses Microsoft Office Excel 2003 or earlier, you cannot use an Excel connection manager to connect to a data source that uses Microsoft Office Excel 2007. Also, you cannot use the Microsoft Jet 4.0 OLE DB Provider to connect to an Excel 2007 data source. To connect to an Excel 2007 data source, use an OLE DB connection manager, and for Provider, select Microsoft Office 12.0 Access Database Engine OLE DB Provider. Then, on the All page of the Connection Manager dialog box, for Extended Properties, enter Excel 12.0.
To load data from or to an Excel 2007 data source, use an OLE DB source or an OLE DB destination, respectively. You cannot use an Excel source or Excel destination to connect to an Excel 2007 data source.
source:http://msdn.microsoft.com/en-us/library/ms139836(SQL.90).aspx
|