Reading an Excel 2007 file from SSIS 2005

Since SSIS uses ODBC to read Excel 2007 files, changing the ODBC connection string to the following:

Data Source=D:\Test.xlsx;Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 8.0;

Data Source=D:\Test.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0


To read an xslm file, I think you need Extended Properties="Excel 12.0
Macro". Similarly, to read an xslx file, you will need "Excel 12.0 Xml".

See the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\ISAM Formats\ for the various providors.