Re: Monitor individual tasks within a DTS package

#1

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 (AT) Feldsien (DOT) com.invalid> wrote

Quote:

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

#2

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:

Quote:

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 (AT) Feldsien (DOT) com.invalid> wrote in message
news:484DD8B9-67B6-46D8-82A6-4BA964E03D59 (AT) microsoft (DOT) com...
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

#3

"Bob Feldsien" <Support (AT) Feldsien (DOT) com.invalid> wrote

Quote:

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?

Yes. You can choose between AND or OR logic, as well as a host of other cool

features.

Quote:

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.
Requires a lot of coding and I think it woudl be much easier to implement

and maintain as lots of tasks.

Quote:

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.

Books
(http://www.sqldts.com/default.aspx?400)

I think they all offer something, I don't really have a favourite. Try the
sample chapters on Amazon or the publishers sites and see which style you
pefer perhaps.

Quote:

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 (AT) Feldsien (DOT) com.invalid> wrote in message
news:484DD8B9-67B6-46D8-82A6-4BA964E03D59 (AT) microsoft (DOT) com...
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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Monitor individual tasks within a DTS package

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Monitor individual tasks within a DTS package - 04-15-2005 , 02:21 AM

Re: Monitor individual tasks within a DTS package - 04-15-2005 , 07:15 AM

Re: Monitor individual tasks within a DTS package - 04-15-2005 , 07:32 AM