Post reply

loosely coupled watchdog over my dw load job

  • July 8, 2024 at 1:03 pm

    #4434136

    Hi we run 2019 standard.  My dw load job is a bit difficult to work with.  It loads data from about 12 erps (one erp per step) each with their own db technologies etc.  somehow it loads a number of threads in ssis dynamically scheduled to run in the proper order for each dimension and fact.  This is done in separate steps one erp at a time.   the job moves on to the next step when a step fails.

    id like to put together a "watcher"(i think there is a sql agent view) that is decoupled from the job but does the following while its running...

    1. reports via email any step that is running longer than an hour.   maybe even more specific unexpected durations depending on the step
    2. reports via email on each step WHEN it fails.  with most errors going to the ssis catalog dashboard i'm not going to try to email the error too
    3. reports via email if the job isnt finished by 5:45 am

    does the community have any thoughts?  i'm looking up that view as we speak.  my recollection is that it isnt always up to date ie current.

     

  • July 8, 2024 at 7:43 pm

    #4434293

    this article seems to talk about that "view".   i'll have to poke around looking for step/job completion status, limitations , latency etc while job is running etc.  i'm a bit uncomfortable not having looked at this for a few years now.

    https://www.sqlservercentral.com/forums/topic/tsql-query-to-show-currently-running-sql-agent-jobs

Viewing 2 posts - 1 through 1 (of 1 total)

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