Many companies are not in a rush to upgrade their SQL Servers because of the enormous cost to upgrade. This results in the majority of companies still running previous versions of SQL Server (2005, 2000, and even earlier). Many times as the developer you are forced to work with older server components but new file sources like Excel 2007 with SQL Server and SSIS 2005. In this case, there are some workarounds that will allow using what seem like two incompatible platforms. This is a highly blogged about topic (including on this site SQLServerCentral) but with some recent questions about it I thought I’d throw one more in the mix.
Before following these instructions ensure that you have the most up to date service packs installed to have to correct data provider for this example.
Create a new OLE DB Connection Manager and select Microsoft Office 12.0 Access Database Engine OLE DB Provider from the Provider list. Then change the Server or file name to the Excel 2007 workbook file path.
Select the All page and change the Extended Properties to Excel 12.0. Then back to the Connection page and hit Test Connection to verify the setup worked.
Now you can use an OLE DB Source in your Data Flow to connect to any sheet in that Excel workbook.