none
Is there a Row limit for Excel ACE Driver?

    Question

  • I have a SSIS package running well in production however sometimes the package will fail when the excel file contains more than 25000+ rows.

    The SSIS package is run by SQL Server Agent and is set to run in 32bit mode.

    I checked the data by loading in batches and all data loaded successfully. But the funny thing is when I run the same package on my local development PC using BIDS and the same data file. The package loads all 25000+ rows successfully.

    Is there some setting that is preventing all rows loading in the server environment.

    Monday, June 1, 2015 5:56 AM

Answers

  • It seems that for large excel files, some temporary folders are created on an additional folder, which needs read write permissions. The folder is

    C:\Users\Default\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO

    Cause on my Local PC I am local admin and have permission to write to this location. On the server permissions are restricted and the service account (account running the sql agent job) and my account do not have access to this folder. Gave the account the correct permissions and now any file will load correctly.

    The full article is here:

    http://www.alankoo.com/2012/09/strange-error-loading-excel-files-xlsx.html


    Thanks.

    • Marked as answer by bdi007 Wednesday, June 10, 2015 7:23 AM
    Wednesday, June 10, 2015 7:23 AM

All replies

  • What is the error message it gives on the failure? Is it xls,xlsx or any other extension like xslm etc? Whats the number of columns involved in this Excel?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, June 1, 2015 6:21 AM
  • Hello - Can you post the package execution result or any error that you are executing while testing this scenario

    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Monday, June 1, 2015 6:35 AM
  • Error message is not very helpful since Sheet1 does exist and is opened successfully when less than 25000 odd rows..

    "Opening a rowset for "Sheet1$" failed. Check that the object exists in the database. "

    Two Excel files having issue -

    File 1 - (File Type = xlsx, Columns = 25, Rows  = 23077)

    File 2 - (File Type = xlsx, Columns = 7, Rows = 30001

    No errors occur when running locally on PC to same environment.

    Entries loaded to ssislog table

    Event
    Start Date/Time
    End Date/Time
    Message
    OnError
    26-May-2015 04:03:19 PM
    26-May-2015 04:03:19 PM
    Opening a rowset for "Sheet1$" failed. Check that the object
    exists in the database.
    OnError
    26-May-2015 04:03:19 PM
    26-May-2015 04:03:19 PM
    SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
    Error code: 0x80004005.
    OnError
    26-May-2015 04:03:20 PM
    26-May-2015 04:03:20 PM
    "component "Excel Source" (2876)" failed validation and returned
    validation status "VS_ISBROKEN".
    OnError
    26-May-2015 04:03:20 PM
    26-May-2015 04:03:20 PM
    One or more component failed validation.
    OnError
    26-May-2015 04:03:20 PM
    26-May-2015 04:03:20 PM
    There were errors during task validation.

    • Edited by bdi007 Monday, June 1, 2015 6:50 AM
    Monday, June 1, 2015 6:43 AM
  • Can you try this?

    Select full data range inside Excel sheet and create a cellrange for it

    Then try referring to the table in the package and see if its working

    http://getsetsql.blogspot.ae/2012/01/using-ssis-load-data-to-excel-sheet-at.html

    Then you can be sure that its not due to some spurious unprintable data that may be present in sheet


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Monday, June 1, 2015 6:55 AM
  • It seems that for large excel files, some temporary folders are created on an additional folder, which needs read write permissions. The folder is

    C:\Users\Default\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO

    Cause on my Local PC I am local admin and have permission to write to this location. On the server permissions are restricted and the service account (account running the sql agent job) and my account do not have access to this folder. Gave the account the correct permissions and now any file will load correctly.

    The full article is here:

    http://www.alankoo.com/2012/09/strange-error-loading-excel-files-xlsx.html


    Thanks.

    • Marked as answer by bdi007 Wednesday, June 10, 2015 7:23 AM
    Wednesday, June 10, 2015 7:23 AM
  • Update, the folder path has changed to C:\Users\Default\AppData\Local\Microsoft\Windows\INetCache\Content.MSO
    Tuesday, April 23, 2019 4:26 PM