Post reply

Excel Files Randomly Failing

  • April 17, 2019 at 9:41 am

    #3423110

    Hello,

    I have a for each loop container that obtains the data from a SQL database and outputs it in different Excel files based on the condition in the loop. Every time I run the package it randomly produces x amount of Excel files then fails with the error "Cannot Acquire Connection From Connection Manager"  ie. sometimes it produces 10 excel files, sometimes 20, sometimes 30 but not the full amount. It clearly connects otherwise it wouldn't be producing some files, however during execution it will randomly fail with this error.

    I have set the RunIn64BitMode property to False and set Delay Validation to True.

    My version of Excel is 2010.

    Any help is appreciated.

  • April 17, 2019 at 11:03 am

    #3424001

    Are there any other errors as well? SSIS often produces multiple errors, especially with an error like that. An error like that is normally accompanied by another.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • April 18, 2019 at 1:40 pm

    #3450961

    I had this problem just last week. The additional error message was "External table is not in the expected format."

    I fixed it by opening the Excel file and then saving it with a slightly changed name.  I have no idea why that worked.

  • April 18, 2019 at 2:47 pm

    #3451853

    The additional error message was the same as @gvoshol -  “External table is not in the expected format.”

    However, I have now fixed the package, although not in the conventional manner, by adding checkpoints at each data flow task. No idea why this worked but it did.

     

  • April 18, 2019 at 2:49 pm

    #3451895

    Thanks - I shall try this next time if need be. Excel destination files seem to behave in strange ways. Perhaps someone can shed some light.

  • April 18, 2019 at 2:50 pm

    #3451928

    Mine was on an import, yours an export.  I missed that before.

     

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

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