SSIS Random Failure reading from Excel

• Ed B

  Hall of Fame

  Points: 3686

  More actions

  July 25, 2022 at 3:03 pm

  #4067924

  planetmatt wrote:

  1. How do you get the package to automatically try the failed file again?
  2. Do I need to set the MaxErrorCount on the Data Flow or the Package?

  I put the data-flow inside a sequence container inside the for each loop, with the on failure pointing to a truncate or delete from destination table then back to the data-flow. When the data-flow fails, it stays within the same sequence container and starts again. I set the MaximumErrorCount > 0 for the data-flow and set the Propagate variable to false in the data-flow event manager. This way the data-flow can fail, but if my delete from destination table fails it will fail the package. The sequence container and everything else retain the default MaximumErrorCount = 0.

  It might we a good idea to put in a 30 second wait before restarting the data-flow to allow the condition that caused the failure to change. This would mean that inside the sequence container the on-failure goes to the delete table, then on success to a sql task with a waitfor delay, plus any logging you might have, and then on success back to the data-flow.

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  July 25, 2022 at 3:42 pm

  #4067948

  then on success to a sql task with a waitfor delay

  Or a script task with a Thread.Sleep().

  ---

• Ed B

  Hall of Fame

  Points: 3686

  More actions

  July 25, 2022 at 3:52 pm

  #4067949

  Phil Parkin wrote:

  or a script task with a Thread.Sleep().

  Cheers for that. I assumed there was a better way of adding a delay, but never looked into it.