How do read data from Excel 2007 with Integration Services 2005? RRS feed

  • General discussion

  • [This is just for sharing information for those common asked questions collected from forums. If you have any better way or feedback, please directly reply in this thread so that community users can benefit from your answers.]

    In Integration Services 2005, when you add an Excel Connection Manager, there is no Excel 2007 and later version in Excel version list. But we do not want to convert the Excel 2007 back to 2003 version. How can I deal with it?

    To read data from Excel 2007, you can try the following steps:
    1. Ensure you have installed the provider “Microsoft Office 12.0 Access Database Engine OLE DB Provider”
    2. Create an OLE DB source, double click on the OLE DB Source and then create an OLE DB connection manager.
    3. Select “Microsoft Office 12.0 Access Database Engine OLE DB Provider” as the provider, and enter the full path of the file for “Server or file name”.
    4. Switch to the “All” tab, specify the extended properties, and then enter “excel 12.0” for the property “Extended properties”. Click the button “Test Connection”, ensure you can access to the Excel file.
    5. After create the Connection Manger, in OLE DB Source Editor, select “Table or view” as the access mode, and then select the Excel sheet in the list “Name of the table or the view”. Then Click the “OK” button.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Monday, June 21, 2010 1:10 PM