none
External table is not in the expected format

    Question

  • Hi,

    I am trying to import the data from excel to my server table when I do this from the import export wizard directly it works fine.

    when I save it as a package and run this as a Sql server job  I am getting the below error

    Message
        Source: MyorderPackage Connection manager "SourceConnectionExcel"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft Office Access Database Engine"  Hresult: 0x80004005  Description: "External table is not in the expected format.".  End Error  Error: 2013-05-14 11:13:33.76     Code: 0xC020801C     Source: Data Flow Task 1 Source - FY13$ [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009. 

    Any suggestions on how to fix this issue.

    Tuesday, May 14, 2013 6:20 PM

Answers

  • I have this check box selected even after that I am getting the error.

    What version of OFFICE and server bitness you are running the package?

    this link would help you

    http://hrvoje.piasevoli.com/2010/09/01/importing-data-from-64-bit-excel-in-ssis/


    Abhinav
    http://bishtabhinav.wordpress.com/

    Tuesday, May 14, 2013 10:01 PM
    Moderator

All replies

  • Hi,

     Please check if Excel file is open during the SQL Server job execution.

    Tuesday, May 14, 2013 7:52 PM
  • No its not open.
    Tuesday, May 14, 2013 7:59 PM
  • Hi,

    1.  Are you running the same package from the same location?

    2. Please open the excel file and save it again with "Xlsx". Run the job again.

    3. If problem exists, then try to save it in "xls". Run the job again.

    If any above of them are not working then we will have to look at from drivers perspective.

    Tuesday, May 14, 2013 8:24 PM
  • Or may be it is the situation when no correct connection string is used:

    Microsoft Office Access Database Engine whereas you use Excel!


    Arthur My Blog

    Tuesday, May 14, 2013 8:26 PM
    Moderator
  • What version of OFFICE you have? what is your server bitness.

    If its a 64 bit machine, and Office 32 bit while setting up job you need to set this property.


    Abhinav
    http://bishtabhinav.wordpress.com/

    Tuesday, May 14, 2013 9:17 PM
    Moderator
  • I tried your suggestions but I still get the same error for the .xls and .xlsx formats.
    Tuesday, May 14, 2013 9:53 PM
  • Even I am not sure why is it displaying Access when my source is Microsoft Excel.
    Tuesday, May 14, 2013 9:54 PM
  • I have this check box selected even after that I am getting the error.
    Tuesday, May 14, 2013 9:55 PM
  • I have this check box selected even after that I am getting the error.

    What version of OFFICE and server bitness you are running the package?

    this link would help you

    http://hrvoje.piasevoli.com/2010/09/01/importing-data-from-64-bit-excel-in-ssis/


    Abhinav
    http://bishtabhinav.wordpress.com/

    Tuesday, May 14, 2013 10:01 PM
    Moderator