Creating a job that monitors a nightly ETL process

Question

Creating a job that monitors a nightly ETL process

shannonjk

SSCarpal Tunnel

Points: 4286
More actions
February 23, 2011 at 12:27 pm

#231579

Greetings everyone.
So after having a few issues here and there with a nightly ETL process, I have decided that I want to create a job that monitors this job. I know there are internal alerts and the like that I can add to the ETL process itself, but for the overall process I would rather have a separate process that monitors and watches the ETL and sends out alerts under certain circumstances as it would be easier to maintain and design.
One of the things I would like it to do however, is to be able to cut off a process if it is taking longer than normal to execute or, more specifically if a step is taking longer than normal to execute.
Every once in a while (I have yet to isolate why) one of the ETL packages seems to get stuck in an infinite loop. When this happens I have to stop it manually and restart the process. Now granted that isn't too difficult but I really don't like waking up at 4am everyday to check this!
Link to my blog http://notyelf.com/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

My first thought was to use the SSIS object model in C# to execute the package and then attach to events that would allow you to end a process early if a certain component was taking too long...but that would put you into a non-trivial C# project.

Ending the component early would only mask the underlying issue anyway...maybe finding the root cause won't be too bad if you have proper logging in place. Have a look at http://dtloggedexec.codeplex.com. It leverages the SSIS object model and lets you log many things happening internal to the pacakge during execution without having to modify your package at all. If you can find and fix the root cause maybe you can avoid having to worry about forcefully ending the process and can avoid those 4AM wakeups.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 2

Thank you for the response, and sorry for the delay in replying to this.

The problem is that there are 4 files my ETL gathers from an access database. For reasons unknown, the network admins and the people responsible for the access database often change the directory path or table names. When ETL goes to connect it just sits in an infinite loop without failing the package. I have it set so if the package fails then the process will keep going (as these tables are not critical for the nightly load) but the problem is the package never fails, it just sits in limbo trying to execute for some reason. I have tried adjusting the time outs to 10 seconds as well as other various things but nothing seems to force the package to fail in this scenario.

Link to my blog http://notyelf.com/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

shannonjk (4/4/2011)
Thank you for the response, and sorry for the delay in replying to this.
The problem is that there are 4 files my ETL gathers from an access database. For reasons unknown, the network admins and the people responsible for the access database often change the directory path or table names. When ETL goes to connect it just sits in an infinite loop without failing the package. I have it set so if the package fails then the process will keep going (as these tables are not critical for the nightly load) but the problem is the package never fails, it just sits in limbo trying to execute for some reason. I have tried adjusting the time outs to 10 seconds as well as other various things but nothing seems to force the package to fail in this scenario.

I might be missing something but can't you write a check to validate the existance of the files and paths before the code really goes to work? For example, xp_DirTree wil return a NULL if the path cannot be found.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Jeff Moden (4/4/2011)
shannonjk (4/4/2011)
Thank you for the response, and sorry for the delay in replying to this.
The problem is that there are 4 files my ETL gathers from an access database. For reasons unknown, the network admins and the people responsible for the access database often change the directory path or table names. When ETL goes to connect it just sits in an infinite loop without failing the package. I have it set so if the package fails then the process will keep going (as these tables are not critical for the nightly load) but the problem is the package never fails, it just sits in limbo trying to execute for some reason. I have tried adjusting the time outs to 10 seconds as well as other various things but nothing seems to force the package to fail in this scenario.
I might be missing something but can't you write a check to validate the existance of the files and paths before the code really goes to work? For example, xp_DirTree wil return a NULL if the path cannot be found.

This is a rather simple solution and easy to implement in SSIS. I would just do something like Jeff has mentioned and then if the dir doesn't exist fail that data flow and move on to the next data flow.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events