• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

Handling failures gracefully in a foreach loop?

I have an SSIS package with a for each loop that runs through a source directory full of log files, checks each one to see if it's locked by another process, and moves it to another folder if it's not.  The whole contraption works but it's pretty ugly from a sql server agent job standpoint as whenever a file is detected as locked or a file already exists in the target directory, it's interpreted as a failure (though I have it set to bypass this and continue to the next file).  Is there any other way to bypass the failures in a more graceful fashion?  When viewing the job history, it looks like the process fails a bunch of times when it's really just false negatives.

Thanks!
Glen
0
jisoo411
Asked:
jisoo411
  • 4
  • 2
4 Solutions
 
Megan BrooksSQL Server ConsultantCommented:
You can prevent the task itself from failing by setting the maximum error count to 0. Unfortunately, this doesn't prevent the error from propagating up the chain and causing the container to fail. To prevent that, create an OnError event handler for the task that fails, add a script task to it, and use the script task to set the variable System::Propagate = false.

If the package is called by another package, you may need additional error handling in the parent package.
0
 
jisoo411Author Commented:
Actually I set the maximum error count = 0 at the container level, so despite failures from the task resulting from locked files or files already existing at the target folder it moves on to the next file and plugs away.  Will the OnError event handler you mentioned work at the container level so that the SQL Server Agent job doesn't look like it failed?
0
 
Megan BrooksSQL Server ConsultantCommented:
Hopefully, yes. Setting System::Propagate = False prevents the error event from propagating to the container (I tested it again last night just to make sure). The only other issue I've heard about is that if the package is called from another package, the calling package may also fail if another event handler isn't provided there.

I did not have time to test with an actual SQL Agent job, and it has been years since I have used this myself, but I think it it will work.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Megan BrooksSQL Server ConsultantCommented:
By the way, you also have the option with the inner task to set the ForceExecutionResult property to Success. I don't think that setting this makes any difference, but I mention it just in case.
0
 
jisoo411Author Commented:
So theoretically setting System::Propagate = False prevents a failure flag from moving up to the parent.  I assume this prevents all failures and not just specific ones?
0
 
Megan BrooksSQL Server ConsultantCommented:
The 'Propagate' flag is not specific to a type of event but the event hander is. Not every event type causes a failure, though -- most do not. As long as the failure is always caused by an OnError event, it should be sufficient to only set the flag in the OnError event hander.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now