Pre and PostExecute Event handler called more than once

I have a task configured on the post execute event handler of a package expecting this task to be executed only once after the completion of all the other tasks in the package. But I found the task configured on the post execute event of the package getting executed as many times as the number of tasks in the package + 1 . Is there any workaround for this problem?

Yes,

The reason this happens is that events "bubble-up" from a container to all parent containers and are caught by any eventhandlers that exist on those parent containers.

To stop this behaviour you'll need to create an eventhandler scoped to each task's OnPreExecute & OnPostExecute eventhandlers and set [@.System::Propogate]=FALSE. This will stop the event "bubbling-up".

-Jamie

Pre and Post Exec Event Handler For a Package

I have a simple test package to play with Event Handler. I got 2 Exec SQL Task and one File System Task. I have set the OnPreExecute and OnPostExecute at package level along with OnPreExecute on SQL and File System Task. These event handlers were loading the package, task name along with start time, machine and user in my audit table (using Exec SQL Task).

OnPreExecute did work fine for SQL and File System Task. They only had one record in the Audit table.

OnPreExecute and OnPostExecute for the package itseld did NOT work. When I say it doesn't work, it logs it to my audit table so many times. I was expecting one record in my audit table for OnPreExecute and one record for OnPostExecute at Package Level. But I was worng. It has inserted into my audit table 14 times instead of twice.

Any ideas? Or am I doing something wrong?

I know I can fix this by workaround. I can remove OnPreExecute and OnPostExecute Event Handler at Package level and insert Exec SQL Task at the beginning and end of the package. This will fix it, but I was wondering why it doesnt work?

Thanks
SuthaI don't quite know why you're getting it 14 times but this will be something to do with the same event getting caught in multiple places.
Every time the File System Task (for example) executes it will fire the eventhandler scoped to the task AND the eventhandler scoped to the package. So your event actually gets handled twice.

-Jamie|||Yeah i concur from our observations that this seems to be what is happening but surely this is a bit of a design issue ....

I want to know from the post execute that the package is starting (or has started), i don't want the startup events from each component thrown up to the post execute event on the whole package because they don't have any relevance at this level.

What do you think ?

Mark Hill|||Jamie

I tried it on a brand new package as well. I can re-produce it. As Mark states that it looks like an design issue. Anyone from MS can shed any light on this?

Thanks
Sutha|||

You can easily debug it like that:
- place a script in package's OnPreExecute event handler
- set a breakpoint on the task (F9)
- run in debugger
- when debugger brakes on the breakpoint add SourceName variable to the Watch window.

Now, every time you hit the breakpoint you will see the name of the task/container that raised the event.

I believe that all OnPreExecute events in your package get propagated to the package level. That's why you see so many of them. If it is not what you expect - you can change it setting Propagate system variable on event handlers to False

possible undetected deadlock

I'm receiving this in my SQL server event log. Although I have trace flag 2004 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?
I'm no expert on the log entry, but the word 'Victim' suggests to me that
this is not a deadlock, since SQL Server has chosen one of the transactions
as victim?
"ET" <et@.et.com> wrote in message
news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl...
I'm receiving this in my SQL server event log. Although I have trace flag
2004 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098)
Value:0x2d5a8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?
|||ET,
What made you work with hotfix build 840? With hotfix builds you can never be sure what you are getting.
Chris Wood
Alberta Department of Energy
CANADA
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl...
I'm receiving this in my SQL server event log. Although I have trace flag 2004 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?
|||What do you think you are getting with traceflag 2004? The flag for trapping deadlocks is 1204.
What exactly are you trying to address?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl...
I'm receiving this in my SQL server event log. Although I have trace flag 2004 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?
|||Sorry. That was a typo on my part. I meant 1204.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:#h#bTTIKEHA.2716@.tk2msftngp13.phx.gbl...
What do you think you are getting with traceflag 2004? The flag for trapping deadlocks is 1204.
What exactly are you trying to address?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl...
I'm receiving this in my SQL server event log. Although I have trace flag 2004 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?
|||It was given to me by Microsoft some time ago to resolve an issue we were having at the time.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message news:ORk$LHIKEHA.2888@.TK2MSFTNGP09.phx.gbl...
ET,
What made you work with hotfix build 840? With hotfix builds you can never be sure what you are getting.
Chris Wood
Alberta Department of Energy
CANADA
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl...
I'm receiving this in my SQL server event log. Although I have trace flag 2004 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?
|||You must be using replication.
Chris
"ET" <et@.et.com> wrote in message news:%23ZgYHcIKEHA.3592@.TK2MSFTNGP09.phx.gbl...
It was given to me by Microsoft some time ago to resolve an issue we were having at the time.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message news:ORk$LHIKEHA.2888@.TK2MSFTNGP09.phx.gbl...
ET,
What made you work with hotfix build 840? With hotfix builds you can never be sure what you are getting.
Chris Wood
Alberta Department of Energy
CANADA
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl...
I'm receiving this in my SQL server event log. Although I have trace flag 2004 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?
|||No
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message news:u4yS86IKEHA.1388@.TK2MSFTNGP09.phx.gbl...
You must be using replication.
Chris
"ET" <et@.et.com> wrote in message news:%23ZgYHcIKEHA.3592@.TK2MSFTNGP09.phx.gbl...
It was given to me by Microsoft some time ago to resolve an issue we were having at the time.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message news:ORk$LHIKEHA.2888@.TK2MSFTNGP09.phx.gbl...
ET,
What made you work with hotfix build 840? With hotfix builds you can never be sure what you are getting.
Chris Wood
Alberta Department of Energy
CANADA
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl...
I'm receiving this in my SQL server event log. Although I have trace flag 2004 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?
|||Ok, so what are you trying to address?
Do you know you have a deadlock? How?
How are you checking the errorlog?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ET" <et@.et.com> wrote in message news:#KRAzaIKEHA.3628@.TK2MSFTNGP12.phx.gbl...
Sorry. That was a typo on my part. I meant 1204.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:#h#bTTIKEHA.2716@.tk2msftngp13.phx.gbl...
What do you think you are getting with traceflag 2004? The flag for trapping deadlocks is 1204.
What exactly are you trying to address?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl...
I'm receiving this in my SQL server event log. Although I have trace flag 2004 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?
|||I was reading the errorlog using xp_readerrorlog when I noticed thse entries.
I am trying to determine if this is some type of undetected deadlock that may be degrading performance in some way.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:esu8SbJKEHA.3920@.TK2MSFTNGP10.phx.gbl...
Ok, so what are you trying to address?
Do you know you have a deadlock? How?
How are you checking the errorlog?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ET" <et@.et.com> wrote in message news:#KRAzaIKEHA.3628@.TK2MSFTNGP12.phx.gbl...
Sorry. That was a typo on my part. I meant 1204.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:#h#bTTIKEHA.2716@.tk2msftngp13.phx.gbl...
What do you think you are getting with traceflag 2004? The flag for trapping deadlocks is 1204.
What exactly are you trying to address?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl...
I'm receiving this in my SQL server event log. Although I have trace flag 2004 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?

possible undetected deadlock

I'm receiving this in my SQL server event log. Although I have trace flag 20
04 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a
8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?I'm no expert on the log entry, but the word 'Victim' suggests to me that
this is not a deadlock, since SQL Server has chosen one of the transactions
as victim?
"ET" <et@.et.com> wrote in message
news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl...
I'm receiving this in my SQL server event log. Although I have trace flag
2004 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098)
Value:0x2d5a8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?|||ET,
What made you work with hotfix build 840? With hotfix builds you can never b
e sure what you are getting.
Chris Wood
Alberta Department of Energy
CANADA
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl
..
I'm receiving this in my SQL server event log. Although I have trace flag 20
04 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a
8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?|||What do you think you are getting with traceflag 2004? The flag for trapping
deadlocks is 1204.
What exactly are you trying to address?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl
..
I'm receiving this in my SQL server event log. Although I have trace flag 20
04 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a
8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?|||Sorry. That was a typo on my part. I meant 1204.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:#h#bTT
IKEHA.2716@.tk2msftngp13.phx.gbl...
What do you think you are getting with traceflag 2004? The flag for trapping
deadlocks is 1204.
What exactly are you trying to address?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl
..
I'm receiving this in my SQL server event log. Although I have trace flag 20
04 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a
8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?|||It was given to me by Microsoft some time ago to resolve an issue we were ha
ving at the time.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message news:ORk
$LHIKEHA.2888@.TK2MSFTNGP09.phx.gbl...
ET,
What made you work with hotfix build 840? With hotfix builds you can never b
e sure what you are getting.
Chris Wood
Alberta Department of Energy
CANADA
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl
..
I'm receiving this in my SQL server event log. Although I have trace flag 20
04 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a
8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?|||You must be using replication.
Chris
"ET" <et@.et.com> wrote in message news:%23ZgYHcIKEHA.3592@.TK2MSFTNGP09.phx.g
bl...
It was given to me by Microsoft some time ago to resolve an issue we were ha
ving at the time.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message news:ORk
$LHIKEHA.2888@.TK2MSFTNGP09.phx.gbl...
ET,
What made you work with hotfix build 840? With hotfix builds you can never b
e sure what you are getting.
Chris Wood
Alberta Department of Energy
CANADA
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl
..
I'm receiving this in my SQL server event log. Although I have trace flag 20
04 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a
8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?|||No
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message news:u4y
S86IKEHA.1388@.TK2MSFTNGP09.phx.gbl...
You must be using replication.
Chris
"ET" <et@.et.com> wrote in message news:%23ZgYHcIKEHA.3592@.TK2MSFTNGP09.phx.g
bl...
It was given to me by Microsoft some time ago to resolve an issue we were ha
ving at the time.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message news:ORk
$LHIKEHA.2888@.TK2MSFTNGP09.phx.gbl...
ET,
What made you work with hotfix build 840? With hotfix builds you can never b
e sure what you are getting.
Chris Wood
Alberta Department of Energy
CANADA
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl
..
I'm receiving this in my SQL server event log. Although I have trace flag 20
04 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a
8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?|||Ok, so what are you trying to address?
Do you know you have a deadlock? How?
How are you checking the errorlog?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ET" <et@.et.com> wrote in message news:#KRAzaIKEHA.3628@.TK2MSFTNGP12.phx.gbl
..
Sorry. That was a typo on my part. I meant 1204.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:#h#bTT
IKEHA.2716@.tk2msftngp13.phx.gbl...
What do you think you are getting with traceflag 2004? The flag for trapping
deadlocks is 1204.
What exactly are you trying to address?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl
..
I'm receiving this in my SQL server event log. Although I have trace flag 20
04 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a
8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?|||I was reading the errorlog using xp_readerrorlog when I noticed thse entries
.
I am trying to determine if this is some type of undetected deadlock that ma
y be degrading performance in some way.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:esu8Sb
JKEHA.3920@.TK2MSFTNGP10.phx.gbl...
Ok, so what are you trying to address?
Do you know you have a deadlock? How?
How are you checking the errorlog?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ET" <et@.et.com> wrote in message news:#KRAzaIKEHA.3628@.TK2MSFTNGP12.phx.gbl
..
Sorry. That was a typo on my part. I meant 1204.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:#h#bTT
IKEHA.2716@.tk2msftngp13.phx.gbl...
What do you think you are getting with traceflag 2004? The flag for trapping
deadlocks is 1204.
What exactly are you trying to address?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ET" <et@.et.com> wrote in message news:e2wuOHHKEHA.3944@.tk2msftngp13.phx.gbl
..
I'm receiving this in my SQL server event log. Although I have trace flag 20
04 enabled, this is all that I see in the SQL log.
Victim Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:80 ECID:175 Ec0x2DD8A098) Value:0x2d5a
8c98 Cost0/270F)
I am running SQL 2000, build 840, on Windows server 2003. Also, I have SQL
configured to use 14GB from a total of 16GB on the server.
Does anyone have any suggestions for how to address this?