THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rushabh Mehta

MS BI geek talk and the community

Gracefully Handing Task Error in SSIS Package

I am sure that there is already some blog or article about this. But, I encountered this today and decided to go ahead and blog anyway. I had a situation, where I expected at certain times (the first of every month), a task to fail, but regardless, I wanted the package to continue to run with success. One other issue, I had, was that I did not want to Error Handler of the package to kick off in case of this task failure. Also, since the task was in a Sequence Container, it was causing the Sequence Container execution results to be Failure thus invoking the "On Failure" workflow.

 

 Default behavior

This is the default behavior where the task failure for the “Drop active jobs partition” SSAS DDL task invokes the error handler at the package level (which is where I have a global error handler) and invokes a failure of its parent container. Since I know that this task is bound to fail the 1st of every month (In this scenario, I am dropping, creating and processing a monthly SSAS partition on a daily which won’t exist on the 1st of a given month), I want to gracefully “ignore” this failure and not signify a failure or write error rows in my error log tables. So, the way to handle this scenario was..

1.       Create an “On Error” Event Handler on the task “Drop active jobs partition” – You can leave the event handler blank with no tasks

On Error Event Handler

2.       To Prevent the error from going further up the chain, open up the System Variables from within the On Error error handler of the task and change the Propogate property’s value to False

Propogate Property

This should provide the desired result where the error is contained within the task

Result

Optionally, you can also choose to set the ForceExecutionResult property of this task to be Success so that it always signals a success – Even with this, you still need to follow the above steps to prevent errors from bubbling up in the package.

Published Thursday, April 24, 2008 8:46 AM by RMehta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ranga said:

Interesting..Thanks for the post

April 24, 2008 4:41 PM
 

Ashwani said:

Hi Rushabh,

This post helped me alot.

Thankyou

Regards,

Ashwani

July 20, 2008 10:49 AM
 

Guna said:

Brilliant post. Simple to the point, and effective.

Thanks. It was just what I needed.

September 30, 2008 12:18 AM
 

RH said:

Aha, what I was looking for.  Thank you!

December 26, 2008 10:20 PM
 

Triaphoo said:

This is smart and wise approach. Thank you!

January 1, 2009 3:20 AM
 

CeeV said:

Wonderful piece of information. I stumbled upon this from the MS groups and this tip relieved me from tons and tons of headache and saved me from jumping into 3rd party solutions as well.

thanks a lot

April 24, 2009 1:11 AM
 

Wolfgang Wagner said:

Thanks for your post! I was on the way to get mad having no solution for this.

July 31, 2009 6:59 AM
 

Brad said:

This was also helpful with the FTP task.  I had a package that was supposed to pull files down from an FTP site (if there were any) and process them, and it worked but the FTP task would report a failure to SQL Server Job Agent every time there were no files to pick up.  Once I figured out where to create an "OnError" handler (under the "Event Handlers" tab, never used that one before) this worked like a charm.  Thank you!

August 12, 2009 3:54 PM
 

James Beresford (aka BI Monkey) said:

Thanks - a neat solution to an annoying problem!

This helped me avoid using the ForceExecutionResult property as I needed to be able to handle errors on a certain task using normal control flow.

August 31, 2009 1:12 AM
 

James Beresford (aka BI Monkey) said:

It is worth noting this doesn't work properly for Parent packages calling child packages, see the latter half of this post: http://www.bimonkey.com/2009/08/preventing-sql-agent-jobs-calling-ssis-reporting-failure/

August 31, 2009 3:48 AM
 

Scott said:

Thank you for your time to share this information!  This is a big help!

January 22, 2010 1:23 PM
 

dbajonm said:

Thanks for sharing.  I've been looking for this solution (and experimenting with my own) for a day and a half.  I wish that I'd stumbled upon this earlier!  I appreciate it.

March 2, 2010 6:25 PM
 

Suresh Chatakondu said:

Nice article. Thanks.

March 19, 2010 3:36 PM
 

bulat said:

that is what I needed, thanks!

July 13, 2010 9:00 AM
 

abhishek said:

thanks rushabh,

this gives me an interest to learn SSIS.

July 13, 2010 6:37 PM
 

Les said:

Don't even want to think about how much time you saved me.

Thank you very much.

August 19, 2010 10:22 PM
 

Otakuman said:

Complete rubbish, didnt work for me at all

September 1, 2010 4:41 AM
 

Andy Button said:

September 22, 2010 8:26 AM
 

James Gardiner said:

Propogate works well if you have a single package , but when you have a parent calling the package you will find that the Parent package will fail. Although the error is stopped from going up the levels in the child package the error will still get passed to the Parent calling package. According to microsoft this is by design.

October 15, 2010 4:13 AM
 

Mitch Small said:

Thanks!

October 15, 2010 10:12 AM
 

TommyPSQL said:

I've used this for months now and it works fine, but for some reason it still signals an error to the sql agent job and causes an additional sql agent failure notification to be sent via email.  Anybody know how to stop this?

October 21, 2010 8:00 AM
 

Søren Andersen said:

Brilliant tip. Thanks.

November 4, 2010 5:12 AM
 

P Cunningham said:

Thank you! Exactly what I was looking for. I am removing Access indexes prior to a large data transfer. I don't want the whole package to report "failed" if those indexes are not there. I implemented this and it worked exactly as I hoped. Perhaps I there is a better/more official way to do this. But at least my hack is doing what I need it to...

November 4, 2010 5:40 PM
 

Emery Swanso said:

This was a fantastic tip. Very complete! Thanks a million!

February 16, 2011 9:59 AM
 

Narayan said:

Thanks,your post really helped me to save a considerable amount of time.

September 19, 2011 11:19 AM
 

Ashwani said:

Thanks for the solution.Saves my precious time.

September 29, 2011 10:59 AM
 

Jo said:

Fantastic! I have been googling for information on this for about 2 hours :)

October 27, 2011 8:31 AM
 

Darius L. said:

Thanks a lot. It helped.

November 28, 2011 4:59 AM
 

Rajesh said:

This is awesome.After googling on too many sites finally i got Solution

December 24, 2011 7:06 AM
 

Geddie said:

Hi.

I feel a bit stupid now, but I have trouble finding out how to perform the following task:

2.       To Prevent the error from going further up the chain, open up the System Variables from within the On Error error handler of the task and change the Propogate property’s value to False

How exactly do you open up the Sytem Variables from within the On Error error handler??

Any tips?

January 13, 2012 7:16 AM
 

Eddy Jawed said:

For a better explanation tot eh same topic, refer to this site!

http://agilebi.com/jwelch/2008/06/29/continuing-a-loop-after-an-error/

Mr Mehta, your effort to explain is noble, but for beginners like me it would be important to add more detail about sequence containers and their event handlers.

March 16, 2012 7:11 AM
 

Rajesh Babu said:

Optionally, you can also choose to set the ForceExecutionResult property of this task to be Success, and You have to set the 'ForceExecutionResult' property of 'Package' also set to 'Success'. then only you will get the success if the package is added in the schedule, otherwise you can see the package is failing. Please set the PACKAGE level value to SUCCESS.

March 16, 2012 8:11 AM
 

Rodrigo said:

This is great, truly clean and easy.

Thanks so much for the post.

April 9, 2012 5:37 PM
 

Saad Subhan said:

Thanks! This was very helpful.

If you want to make sure that the Task failure was caused by the reason that's acceptable to you, then you can add a script task in the OnError of the task and check the value of System::ErrorDescription.

This way, if the task failed for some other reason, the error will be propagated as usual.

string err = Dts.Variables["System::ErrorDescription"].Value.ToString();

if (err.Contains("File represented by") && err.Contains("does not exist"))

{

               //change propagate to false

               Dts.Variables["System::Propagate"].Value = false;

August 1, 2012 10:45 AM
 

Vaishnavi said:

Didn't work for me. Could you please explain why?

August 16, 2012 3:58 AM
 

G3 said:

Thanks so much, helped save my time so much!

August 16, 2012 1:38 PM
 

Steve said:

Late to the party, but exactly what I was looking for. Thank you!

September 13, 2012 11:34 AM
 

Kannan said:

Thanks and it worked as expected.

October 12, 2012 8:26 PM
 

Ajay said:

Thanks. It helped immensly

October 19, 2012 6:32 PM
 

Shyam said:

Very Useful. Thanks

October 25, 2012 5:26 AM
 

John said:

This does not work.  I'm sorry but this was a useless blog post; please post valid solutions next time.

March 4, 2013 2:31 PM
 

BKRAO said:

Thanks so much, helped save my time

March 6, 2013 11:31 PM
 

satish kumar said:

Hi Rushabh Mehta !

Thank you so much for your Great Tip.

It worked for me.

March 28, 2013 9:14 AM
 

ed said:

I used Rajesh Babu's recommendation and it was infinitely easier.

"  

Rajesh Babu  said:

Optionally, you can also choose to set the ForceExecutionResult property of this task to be Success, and You have to set the 'ForceExecutionResult' property of 'Package' also set to 'Success'. then only you will get the success if the package is added in the schedule, otherwise you can see the package is failing. Please set the PACKAGE level value to SUCCESS.

"

April 6, 2013 11:43 AM
 

JohnT said:

Well done Mr Mehta. Great tip which works and I keep on needing to use.

May 15, 2013 12:10 PM
 

mahmood said:

thank you its ve useful for me

July 24, 2013 2:35 AM
 

Gourav Verma said:

thank you , for your help , it's very useful information

February 13, 2014 10:32 AM
 

Mark said:

Many thanks. Good solution. A better solution would be for SSIS to be designed in a way that wasn't so darned obscure. Or even designed, rather than evoleved!

Anyway, many thanks.

February 27, 2014 4:50 AM
 

Alan G said:

One of the most useful tips on SSIS i have read - and it works a treat. Thank you so much.

March 27, 2014 7:48 AM
 

Thuan N said:

Thank you so much for your solution.

April 6, 2014 9:55 PM
 

Thuan N said:

Thank you so much for your solution.

April 6, 2014 9:55 PM
 

Yogesh said:

Is there any way to know the name if the child package from which the even is bubbling up to the parent package and finally raising event in the parent package?

April 16, 2014 3:23 AM
 

Rashid said:

thanxs

April 30, 2014 4:18 AM

Leave a Comment

(required) 
(required) 
Submit

About RMehta

Rushabh is an Independent Consultant and a Business Intelligence Mentor for Solid Quality Mentors. He has been the lead architect for many large scale and complex Business Intelligence solutions for large enterprise clients such as Publix, Raymond James Financials, Jackson Hewitt, Checkers and most recently on a multi-terabyte Health Services solution for the US Department of Defense. Additionally, he has been a mentor for a large number of client such as Veterans Health Administration, Intuit, Zillow.com, Cymer, Navy Medical Center, Talbots, FM Global and Sunoco. Rushabh is also a BI trainer and has taught BI courses in countries around the world. Through his work experience, Rushabh has been instrumental in creating multiple best-practices in the implementation of the BI technologies. Rushabh also serves on the Board of The Professional Association for SQL Server (PASS) as the President. Rushabh is also a SQL Server MVP and a frequent speaker at large conferences as well as PASS Chapters around the world.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement