Post reply

SSIS package loading more than 25000 rows using Excel ACE 12 Driver

  • June 1, 2015 at 12:07 am

    #304549

    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.

    Thanks Brian

    You are never an expert, you are always learning!

  • June 1, 2015 at 12:42 am

    #1801920

    what error you are getting?

  • June 1, 2015 at 12:55 am

    #1801922

    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.

    Thanks Brian

    You are never an expert, you are always learning!

  • June 1, 2015 at 1:25 am

    #1801924

    Its a validation level issue, means when ssis validate the source connection existence.

    Try the Delay Validation property set to 'True'.

  • June 10, 2015 at 1:29 am

    #1804227

    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 Brian

    You are never an expert, you are always learning!

Viewing 5 posts - 1 through 4 (of 4 total)

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