Using Excel 2007 files as a Source in SSIS 2005

Thanks to “jaegd” for his post here: http://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).

Steps

  1. Create new OLEDB Connection by right clicking on the Connection Manager tray
  2. New OLEDB Connection in SSIS

    New OLEDB Connection in SSIS

  3. Chose any valid type or value for the connection and click okay
  4. OLEDB Connection Manager

    OLEDB Connection Manager

  5. Rename the Connection Manager to something that makes sense, like “Excel 2007”
  6. Now that the the connection exists right click on it and select properties
  7. 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;”
  8. manually-edit-connection-string

    manually-edit-connection-string

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!

Cheers,
Ben