I am programmatically loading and executing packages from VB.NET. When
ActiveX script tasks instance objects, such as ADO, and those objects
fail, the only error indication I get is a GENERAL DTS error. The error
of the underlying object, from the script, is lost. For example, ADO
COMMAND TIMEOUT.

In the VB.NET code which executes the package, I trap exceptions at
execution, I report all available error indicators I can find, and I do
a post-mortem dump of the error data in the steps, but no where do I see
the scripted object error indicator.

The pattern seems to be when ActiveX instances an object, and IT has an
error, then it perculates up through DTS as a "general error", which
hides the real problem. Is there ANY way to get more diagnostic
information when executing programmatically?

I am sure this must have been asked before, but I researched some FAQ
and could not find it.

In message <uF1caW0yEHA.2316 (AT) TK2MSFTNGP15 (DOT) phx.gbl>, Lee Gillie
<Lee (AT) nospam (DOT) odp.com> writes
Try using events, as sometimes this can give you more info than the
GetExecutionErrroInfo method from the step.

HOW TO: Use DTS Package Events in Visual Basic .NET
(http://support.microsoft.com/default...b;en-us;321525)

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

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

Hi Darren -

Thanks for responding!

I have logging in place for ALL package events, including OnError. This
is the information I see in that event when an ADO command object
times-out when executing in an ActiveX script.

ErrorCode: -2147220482
Source: Microsoft Data Transformation Services (DTS) Package
Description: General error -2147220482 (800403FE).

I have observed if there are syntax errors in the script, for example,
that I get some very nice diags telling exactly what line of the script
is bogus, and information about the problem. Or when transformations
fail, it is very helpful information. DTS reports errors from his own
objects very nicely. The only thing the DTS script execute engine seems
to obscure are errors which occur in objects instanced by the ActiveX
script code (such as ADO).

After the failed package execution I also record the
GetExecutionErrorInfo from every step, but nothing more revealing here
either.

Is this a known problem with DTS, for which there is no solution? When
ever I see this "general error" being discussed, responders always talk
around the fact that the indicator is useless, and focus more on finding
hints about the specific problem in other ways. At first I thought I was
just too dumb to see where the error indicator I needed was found. But
now I am beginning to think this is perhaps a design shortfall of DTS,
and no one likes to talk about it.

I wrote an ActiveX script host some years ago, and I know it IS possible
for script hosts (such as the DTS ActiveX Task) to get information about
COM errors down in objects of the script. DTS could do a better job
here. I can't find anything in the KBase about this, so it makes me
wonder if anyone has ever complained about this before.

Lee Gillie
Online Data Processing, Inc.
Spokane, WA


Darren Green wrote:

In message <#FNdYH2yEHA.2980 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Lee Gillie
<Lee (AT) nospam (DOT) odp.com> writes
I think this is just a shortfall of DTS, something I have noticed myself
anecdotally, but never really dug into it.

One thing you could try is to catch the error using On Error Resume
Next, and check the Err object and any error collections such as for
ADO, then raise your own error. Things like ADO errors are one step down
from a script error, so I guess this is the problem.

Personally I find the log to SQL Server option the best way all round to
get error information, but I don't use ADO in script, coz it is such a
pain to manage and debug. You could query these tables from your app.

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

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