Thanks to “jaegd” for his post here: forums.microsoft.com/… on how to accomplish this. Below please find some more details along w/ a example which can be downloaded here (ssis-2005-excel-2007-source-example).
- Create new OLEDB Connection by right clicking on the Connection Manager tray
- Chose any valid type or value for the connection and click okay
- Rename the Connection Manager to something that makes sense, like “Excel 2007”
- Now that the the connection exists right click on it and select properties
- Edit the Connection String property IN THE PROPERTIES WINDOW to match below, updating with your Excel File Location
- Data Source=C:MyExcelFile.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=”Excel 12.0;HDR=YES;”
You can now use this connection by referencing it with any task/object that would normally connect to an OLEDB source. To query it use [Sheet1$] for the table name (eg. SELECT * FROM [Sheet1$])
Do you have anything to add? Please add your comments below!