November 17, 2016 at 4:42 am
I have an SSIS package to load our data warehouse overnight. This package calls a series of child packages that for the most part are based on Execute SQL tasks to load the staging and fact tables. There is an OnPostExecute event handler on the master package that writes to a logging table after each task. Checkpoints are in place and they are set to IfExists. The Master package is called from a SQL Agent job which currently runs under sa privileges (That's another story so please don't shoot me for it). There is error handling in place to stop the job if necessary.
What I can't work out is why the event handler doesn't write to the logging table if the package is stopped at any point. The event handler will enter a row in the table on completion of every task in the package when the package is running in normal conditions. However, if the package is stopped and restarted at any point it no longer logs to the table. This happens whether the job is stopped as part of the error handling process or if it is manually stopped.
Can anybody shed any light on this please? The logging table is most useful when there's been an error so I can see what has run and make sure that everything runs successfully. If things aren't being logged correctly it's much more difficult to what's happening.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
November 17, 2016 at 5:43 am
BWFC (11/17/2016)
I have an SSIS package to load our data warehouse overnight. This package calls a series of child packages that for the most part are based on Execute SQL tasks to load the staging and fact tables. There is an OnPostExecute event handler on the master package that writes to a logging table after each task. Checkpoints are in place and they are set to IfExists. The Master package is called from a SQL Agent job which currently runs under sa privileges (That's another story so please don't shoot me for it). There is error handling in place to stop the job if necessary.What I can't work out is why the event handler doesn't write to the logging table if the package is stopped at any point. The event handler will enter a row in the table on completion of every task in the package when the package is running in normal conditions. However, if the package is stopped and restarted at any point it no longer logs to the table. This happens whether the job is stopped as part of the error handling process or if it is manually stopped.
Can anybody shed any light on this please? The logging table is most useful when there's been an error so I can see what has run and make sure that everything runs successfully. If things aren't being logged correctly it's much more difficult to what's happening.
I'm not certain, but I don't think that the PostExecute event fires if execution is interrupted – and that would explain the behaviour you are seeing. I suggest you add an OnError handler too.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 17, 2016 at 7:04 am
Thanks Phil. It's really frustrating that it doesn't continue to log.
There is an OnError event handler in place and it's that that stops the job. It does do some logging but not as much as I like.
What I have discovered in the last ten minutes is SSIS own built-in logging. It seems to do exactly what I want and is much more stable than the hand-rolled stuff. Unless there's a good reason not to use it, I think I'm going to head down that route.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
November 17, 2016 at 7:43 am
BWFC (11/17/2016)
Thanks Phil. It's really frustrating that it doesn't continue to log.There is an OnError event handler in place and it's that that stops the job. It does do some logging but not as much as I like.
What I have discovered in the last ten minutes is SSIS own built-in logging. It seems to do exactly what I want and is much more stable than the hand-rolled stuff. Unless there's a good reason not to use it, I think I'm going to head down that route.
I use a combination. My own logging for simple job start/stop info and custom errors/warnings/info messages & then SSIS logging for unexpected error debug sessions.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 17, 2016 at 7:49 am
I'll probably go that way myself. The OnError event handler writes to a table and sends out an email with the details so I can capture what went wrong. It was just not being able to see what was running when I restarted the job that was annoying.
At least I've got something to go on now.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply