Excel Files Randomly Failing

  • sanaan-sheikh

    Valued Member

    Points: 68

    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.

  • Thom A

    SSC Guru

    Points: 98273

    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.

  • gvoshol 73146

    Hall of Fame

    Points: 3092

    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.

  • sanaan-sheikh

    Valued Member

    Points: 68

    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.

     

  • sanaan-sheikh

    Valued Member

    Points: 68

    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.

  • gvoshol 73146

    Hall of Fame

    Points: 3092

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

     

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

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