Post reply

SSIS - Data Load Using Excel Source

  • December 18, 2019 at 11:28 am

    #3708741

    Hi All,

    I am trying to load Excel(2017) data into SQL Server using SSIS. When i try to load directly , it is throwing error.

    Error : External Table is not in the Expected Format

    But when i open and saving that same excel file as .xlsx format it loads without error.

    How to solve this issue?

  • December 18, 2019 at 12:49 pm

    #3708759

    Check your Excel source if it's for the right version of Excel file

     

    excel

  • December 18, 2019 at 1:17 pm

    #3708761

    Dinors,

    When i select the excel file  the excel version is automatically changed to "Microsoft Excel 2007-2010"

  • December 18, 2019 at 1:31 pm

    #3708765

    Please expand on this:

    But when i open and saving that same excel file as .xlsx format it loads without error.

    What format was the file in before you opened and saved?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 18, 2019 at 1:35 pm

    #3708788

    Same .xlsx format only in both operation

  • December 18, 2019 at 2:11 pm

    #3708804

    have you verified on the file properties before and after opening & saving the xslx file that it's for the same version of Excel?

  • December 18, 2019 at 5:16 pm

    #3708859

    Can you try to verify the actual file type before you open it in Excel?  I've seen some people do silly things like name a plain text file with an Excel file extension just so that it becomes the default program to open it.  Excel allows this and will try to figure out the format then.  SSIS is more strict and if you tell it the file is Excel workbook it must be.  Maybe try opening the file with notepad or something to see what it looks like?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply