SSIS - Excel 2007 .xlsx files as source

By default Excel 2007 (.xlsx) files cannot be read by SSIS. Office 2007 uses Microsoft Access 12.0 Access Database Engine OLEDB Provider (ACE) and not Microsoft Jet 4.0 OLEDB provider. You can download and install this drive from
http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

If you happen to use OLEDB Source and try to access Excel 2003 and below files using Jet 4.0 driver, by default Jet 4.0 OLEDB Provider expects .mdb file.We have to explicitly instruct Jet 4.0 to read excel files (.xls). This can be done my keying in "Excel 8.0" in the "Extended Properties" property in the "ALL" tab of the Datalinks form.



Similarly for Excel 2007, we have to use ACE 12.0 OLEDB Provider and specify "Excel 12.0" in the Extended Property