On Sep 9, 9:12 am, tech...@gmail.com wrote:
> In a DTS package, it is a laborious task to connect an On Failure
> Workflow to a different Send Mail Task for each task (major or minor).
> I am trying to do some researching on a single effective way of
> sending an email with a description of the error and the job->task
> that it belonged to.
>
> What I thought could be possible is to have an On Failure Workflow
> coming from each of the tasks in a package pointing to an ActiveX
> script that can gather the Job (if applicable), DTS Package and Task
> name, error code, message and description, and the time of the error
> and put it into a temporary global variable, then use that to make up
> the subject and body in a Send Mail Task. Though I have never done
> anything like this in DTS before.
>
> The down point here is that there will be a very messy DTS diagram at
> the end with all the candy red arrows pointing to the ActiveX task.
> Slightly more agreeable than having a separate Send Mail task at the
> end of each red arrow.
>
> Is there a better way to do this?

On 11 Sep, 23:16, Jay <JasonPir...@gmail.com> wrote:
> On Sep 9, 9:12 am, tech...@gmail.com wrote:
>
>
>
>
>
> > In a DTS package, it is a laborious task to connect an On Failure
> > Workflow to a different Send Mail Task for each task (major or minor).
> > I am trying to do some researching on a single effective way of
> > sending an email with a description of the error and the job->task
> > that it belonged to.
>
> > What I thought could be possible is to have an On Failure Workflow
> > coming from each of the tasks in a package pointing to an ActiveX
> > script that can gather the Job (if applicable), DTS Package and Task
> > name, error code, message and description, and the time of the error
> > and put it into a temporary global variable, then use that to make up
> > the subject and body in a Send Mail Task. Though I have never done
> > anything like this in DTS before.
>
> > The down point here is that there will be a very messy DTS diagram at
> > the end with all the candy red arrows pointing to the ActiveX task.
> > Slightly more agreeable than having a separate Send Mail task at the
> > end of each red arrow.
>
> > Is there a better way to do this?
>
> I've taken a somewhat different approach to this problem. Due to the
> nature of my group we have hundreds of etl type jobs that run
> everyday. So I created a "job monitor" job that runs every hour and
> reports the job names that have failed with an attached .csv file
> containing the step that failed with a description. I did this with a
> couple of stored procedures that query the msdb..sysjobs and
> msdb..sysjobsteps. Take a look at those tables and I think you find a
> solution that can be developed to cover all jobs rather than having
> each package and job require all the extra work to report issues. If
> you need more help let me know. Good luck.
>
> Jay- Hide quoted text -
>
> - Show quoted text -