I am calling a DTS from a stored proc.

when I FORCE a task to fail (testing) the following code
does not return an error...it will always return 0
therefore never going inside the if statement.

--Executing the Package:
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
SET @strReturnMsg = '*** DTS Package Execute failed'
--EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN -1
END

I know the 'ActiveX' task fails because the DTS does not go on to
the next task a 'Bulk Insert'.


thanks

nevlis

Forgot to mention SQLSERVER 2000 sp.3

nevlis <silven (AT) removesilven (DOT) canada.com> wrote in
news:Xns95E17EBDB8F4Csilvencanadacom (AT) 206 (DOT) 167.113.5:

In message <Xns95E181939CE73silvencanadacom (AT) 206 (DOT) 167.113.5>, silven
<silven (AT) removesilven (DOT) canada.com> writes

The package executed just fine, from a COM perspective, so no error is
detected where you expect. The failure of a package is internal to that
package and does not get throw all the way out again. You need to check
the execution result for each step to see if any steps failed within the
package.

For an example of doing this see the spExecutePKG and spDisplayPKGErrors
procedures here-

Archive
(http://www.sqldts.com/default.aspx?273)

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

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

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

Thanks