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
jisoo411Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.