Search code examples
sql-serverexcelssisetlssis-2016

SSIS loading Excel file

This is my first time using SSIS, I'm getting this error.

Using: VS 2015, Excel 2010

Exception from HRESULT: 0xC02020E8 Error at Data Flow Task [Excel Source [1]]: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.

Error at Data Flow Task [Excel Source [1]]: Opening a rowset for "'R0270 Cases$'" failed. Check that the object exists in the database.

The first 7 rows in the excel file are headers information. Row 8 has the column labels and data starts at 9.

Excel Source Editor:

Data access mode: Table or View

Excel Sheet drop-down, it errors out for the first 30 seconds then it populates with the sheet name.

Any clue what's happening and how can I skip the first 7 rows. I have 100+ excel files and same thing happens on 8 different files.

Thanks for your assistance.

Solution

  • Error possible solutions

    1. You have to add IMEX=1; to your excel connection string, to helps excel to identify the data types correctly
    2. Set the DelayValidation property of the OLE DB Source adapter to True.
    3. Try removing spaces from sheet and excel file name

    Reading from row 8

    Assuming that you have 4 columns

    1. In the DataFlow Task, double click on the excel source Set the source to SQL Command
    2. Use the following command: SELECT * FROM [R0270 Cases$A8:D] , so it will start reading from the row number 8 (D means the column number 4 in excel)

    References