Hello,
I have a series of DTS Packages that perform multiple tasks, including
a few data pumps. I want to be able to trap a specific error (Error
appears in the log) and perform some possible cleanup or retry logic
before the entire package fails. Currently I am just forcing a
failure of the package. Can I use an ActiveX Script task that flows
from the failue of my data pump task, evaluate what errors occur and
decide what workflow to follow from there?

Any other ideas?

Thanks,

Ed C.

In message <15ef71f5.0402101152.541200ae (AT) posting (DOT) google.com>, Ed
<ed_castaneda_oh (AT) yahoo (DOT) com> writes
You can use On Failure workflow constraints between tasks, but there
isn't any easy way of detecting what the error actually was. In theory
you could turn on logging to SQL Server and query the log tables for the
current execution (turn on lineage to get the global variables which
make it easier).

The best way by far of handling errors in Dts is to test for error
conditions before the e come errors, and handle them appropriately. For
example check that a file exists before you import it. For example-

How can I check if a file exists?
(http://www.sqldts.com/default.aspx?211)



--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren,

Thank you for your reply. Unfortunately I don't think that I can do
that, at least currently the way our packages are setup. We have
transformations that are loading data from a BasisPlus DB on the VAX
to a SQL Server 2000 DB. The error occurs in the ODBC connection and
it is intermittent.

I already have an AxtiveX script task that flows from the OnFailure
event from that DataPump task. I want to be able to check the error
for the DataPump task and if it is the intermittent BasisPlus ODBC
connection problem, I want to retry that step again. (99% of the time
when the error occurs, a second try works ok)

I guess I can look into querying the Error Log like you suggested. I
was just hoping there was a way to access a previous step's error
collection and trap a certain error.

Any other ideas?

Thanks,

Ed

Darren Green <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

In message <15ef71f5.0402160741.55c46cb9 (AT) posting (DOT) google.com>, Ed
<ed_castaneda_oh (AT) yahoo (DOT) com> writes
I see what you want, and the log would probably work.

There is no simple way in pure DTS to get that error info. The method
GetExecutionErrorInfo method which can get the step error info cannot be
called directly from ActiveX script because it uses ByRef parameters of
types other than variant. (ByRefs must be typed correctly to work, and
you only have variant, not any other type in VBScript).

So another solution would be to work around this limitation, using a
simple COM object that you pass in the step, and pass out the error info
as properties or variant ByRefs, wrapping up GetExecutionErrorInfo
basically. You could take this one step further and put all your logic
inside this object, or a step further again would be to write a custom
task. This could actually contain all logic including that required to
detect the error, and restart the data pump if it is the error of
interest.

Another idea, assuming this is scheduled job is just to set the job step
retry count > 0. Of course this would not take account of the error
itself, just any error, but it may be good enough, and certainly simpler
to implement.

Darren


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren,

Thanks again for your response. I was afraid of the situation in
which you describe. I was looking for a quick and dirty solution
within DTS, but it looks like I am going to have to do something
special if I want to "correctly" fix my problem.

Thanks again,

Ed


Darren Green <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote