OnError Event Handler

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » OnError Event Handler

OnError Event Handler

Rate Topic

Display Mode

Topic Options

Author

Message

venus.pvr

Posted Monday, December 13, 2010 10:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:52 AM
Points: 109, Visits: 396

Hi,

I have a package which calls Package1 and Package2. I have implemented an event handler which fires OnError,
I have a ExecuteSQL task that logs the Error into a table.

There is an error in Package1. But in the table I could see that there are two entries.

When I used breakpoints to debug I could see that the eventhandler is being called twice.

It shows me the same behavior for both OnError and OnTaskFailed events.

Please advice..

Thanks and Regards,
Praveena.

Post #1033937

Daniel Bowlin

Posted Tuesday, December 14, 2010 2:07 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 2,673, Visits: 2,418

Is the error being logged by one of the child packages and then rolling up to the parent package and being logged again?

Post #1034743

venus.pvr

Posted Tuesday, December 14, 2010 7:24 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:52 AM
Points: 109, Visits: 396

I am logging it both in the child package and parent package, ON Error. But the package Names will be different in the rows. And after running the package I see a total of 4 entries in the table.
ChildPackage Failed
ParentPackage Failed
ChildPackage Failed
ParentPackage Failed

I tried both On Task failed and OnError and it shows the same behavoir.

Thanks and Regards,
Praveena.

Post #1034856

Raunak Jhawar

Posted Wednesday, December 15, 2010 11:44 PM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:25 PM
Points: 946, Visits: 1,739

venus.pvr (12/13/2010)

Praveena,

This is very normal. These OnError event handler will trigger on every error encountered during the package life cycle.

One approach would be to to use a sequence container encapsulate your package and apply appropriate handlers on Seq Cont. Also do not forget ton set appropriate properties of SEQ container and it's child packages

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum

Post #1035663

venus.pvr

Posted Thursday, December 16, 2010 7:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:52 AM
Points: 109, Visits: 396

Raunak,

I have one error in the parent package and one error in the child package. In that case the handler should be only called once for the parent package and once for the child package. But the handler is invoked two times for the parent package and two times for the child package. Is this a normal behavior. Or is there any property that needs to be set to the packages?

Thanks and Regards,
Praveena.

Post #1035848

venus.pvr

Posted Thursday, December 16, 2010 12:25 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:52 AM
Points: 109, Visits: 396

Raunak/Daniel,

In my parent package, the child packages are all executed in a sequence container. And probably that is the reason why it records the error twice, once for the Execute package task and other for the sequence container.

Is there any way I could avoid it? I have 12 child packages that execute one after the other in the Sequence container. The constraint between the Execute Package task packages is "On Completion".
I need one entry for each of the child packages on error.

Thanks and Regards,
Praveena

Post #1036052

rashmiptl22

Posted Monday, December 20, 2010 3:33 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, December 12, 2011 12:36 AM
Points: 23, Visits: 309

Hi Praveena

Do you know the use of propagate variable in SSIS?

If we are using the event handler for eg.OnError for ExecutePackageTask1 and error Occures in ExecutePackageTask1 then the OnError event will be fired for the task that genatates the error and then the error will be propagated to it's parent container or task in a hierarchial fashion.
If you set Propagate variable value of ExecutePackageTask1 to false then the error will not be propagated to it's parent.

To set the value of propagate value follow the following steps

Select the ExecutePackageTask1
select the EventHandler tab
Click the "Show System Variables" icon from the window that displays list of all variables
Select the "propagate" variable ,By default it's value is "True".Set it to False

Incase any query let me know

Thanks and Regards
Rashmi

Post #1037100

venus.pvr

Posted Tuesday, December 28, 2010 8:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:52 AM
Points: 109, Visits: 396

Hi Rashmi,

That worked for the child package. But, for the parent package, it still shows the same behaviour.

Thank you for your inputs. It helped.

Post #1039822

rashmiptl22

Posted Wednesday, December 29, 2010 3:31 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, December 12, 2011 12:36 AM
Points: 23, Visits: 309

Hi Praveena

I think this solution will also work for your ParentPackage.Can you please explain in detail what problem you are facing with your Parent Package.

It will be good if u can send a sample project ,so that I can have a look into it.

Regards
Rashmi

Post #1040176

venus.pvr

Posted Wednesday, January 05, 2011 12:22 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:52 AM
Points: 109, Visits: 396

Hi Rashmi,

It was logiing twice for the parent package because it actually had 2 errors with in the same task.

Error 1: Cannot open the datafile "S:\DB\xfer\Process\0000_ow_master_12042010_100005.unl1".

Error 2: component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC020200E.

Thank you for all the info.

Praveena

Post #1043284

« Prev Topic | Next Topic »

Permissions