Re: Monitor individual tasks within a DTS package

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I certainly agree that this is a hassle. As you noted, multiple constraints
to a single task never fire. This strikes me as a design flaw in DTS itself.
Do you know if MS has addressed this in the next SQL Server version?

I think the next thing I will try is to add some logic in the ActiveX script
tasks that will redirect the package flow to the EMail task in the event of a
failure, rather than using multiple Failure constraints.

I'm not as experienced with DTS as I would like, and frankly find the MS
documentation to be somewhat superficial. I wonder if you, or any of the
other readers, could recommend a book that will help me quickly bring my
skills up to the next level.

Thanks,
Bob

"Darren Green" wrote:

> You will need to use a separate mail task or "error handling task" per
> worker task. Use an On Failure constraint to link the two. Multiple
> constraints to one task will not work as the workflow engines uses AND logic
> in this scenario.
>
> Personally I find this too much hassle, but if you set package logging on to
> SQL, and have a second step to your job that runs after the package you can
> query the log and extract errors to send mails for any failures. This offers
> a single error check regardless of where in the package you experience a
> failure.
>
>
> --
> Darren Green
> http://www.sqldts.com
> http://www.sqlis.com
> "Bob Feldsien" <Support@xxxxxxxxxxxxxxxxxxxx> wrote in message
> news:484DD8B9-67B6-46D8-82A6-4BA964E03D59@xxxxxxxxxxxxxxxx
> > I have several fairly complex DTS packages that need to send an email not
> > only upon completion, but also upon failure of any of the individual tasks
> > within the package. The individual tasks update certain global variables
> > that must be included in the body of the EMail.
> > So, the solution I have tried is to set up a DynamicData task to update
> the
> > parameters for an EMail task. I can get the Dynamic Data task to fire
> upon
> > completion of the last actual step in the package, however I can't get it
> to
> > work upon failure of a previous task.
> > Any suggestions and/or sample code would be greatly appreciated.
> >
> > Thanks,
> > Bob
> > --
> > Bob Feldsien MCSD, MCDBA
>
>
>
.

Relevant Pages

  • Re: Monitor individual tasks within a DTS package
    ... > tasks that will redirect the package flow to the EMail task in the event ... > failure, rather than using multiple Failure constraints. ...
    (microsoft.public.sqlserver.dts)
  • Re: catch replacing useful errorInfo
    ... I can agree on all that as good general advice. ... failure, I return a message to the client (actually the application ... If a buggy package is added to the system, ... Third example allows silent failure of a documentation proc. ...
    (comp.lang.tcl)
  • Re: Help: Multi-table design
    ... Each type of package required tracking a different set of dates. ... JobID INTEGER NOT NULL, ... BTW those CHECK constraints can be replaced using the Table Validation ... INSERT INTO JobStateTrasitions (job_type, previous_state, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: DTS package succeeds, but log indicates failure
    ... Task failure does not mean package failure. ... "Fail package on step failure" option can be set on ... the log file it says that the pacakge as a whole has failed, ...
    (microsoft.public.sqlserver.dts)
  • Re: Run Task at the End of the Package?
    ... naturally - I might suggest you wrap it using a STEP 2 from a SQL_JOB ... that executes the package; in that case you can signal that on JOB STEP ... On failure, you have to do something or quit. ... This is for SQL 2000 DTS. ...
    (microsoft.public.sqlserver.dts)