DTS Error Handling

Hi folks,

I would like to know -

-) Is there a way to continue the DTS Package execution even if there are some errors? for example if there is a primary key violation error, I would like to continue with my transformation.

Note:
I found a KB (#240221) in MSDN "HOW TO: Handle Errors in Data Transformation Services "Package" and "Step" Objects". At the bottom of this article MS suggests to use DTSErrorMode object. But I could not find that object.

DB version : SQL Server 2000You can handle exceptions in DTS tasks and continue processing...

The example you gave: PRIMARY KEY Violation would most likely occur in an Execute SQL Task or possibly an ActiveX Script.

When an error occurs in a task you can add an On Failure Workflow to an ActiveX Script that restarts the process (loops back to) where it left off, i.e. performing the next INSERT etc...

See the sample below:

Function Main()

dim pkg
dim stp
'Rerun the ActiveXScriptstep
set pkg = DTSGlobalVariables.Parent 'Get a reference to the package
set stp = pkg.Steps("DTSStep_DTSActiveScriptTask_1")
stp.ExecutionStatus = DTSStepExecStat_Waiting

'Release resources
set stp = nothing
set pkg = nothing

main = DTSTaskExecResult_Success
End Function

Regards,
CPN

DTS

Hi folks,
I am running an Active-X in DTS package and it works perfect when execute
from the DTS design area but it fails when run as JOB. This is the error i
get. Any idea? I couldnt figure out the clue from this error msg. Any help
appreciated. TIA
************************************************** *********
Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
string: The task reported failure on execution. Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 1100 Error Detail Records: Error:
-2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
reported failure on execution. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp Help
context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun:
Package execution complete. Process Exit Code 1. The step failed.
************************************************** ***********
Either because it's now runnnig as user ABC\SYSTEM rather than as your login
or because all paths are now relative to the server (and using different
mapped drives) instead of relative to your workstation.
Those are the usual problems when executing on the server.
"rupart" wrote:

> Hi folks,
> I am running an Active-X in DTS package and it works perfect when execute
> from the DTS design area but it fails when run as JOB. This is the error i
> get. Any idea? I couldnt figure out the clue from this error msg. Any help
> appreciated. TIA
> ************************************************** *********
> Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
> DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
> string: The task reported failure on execution. Error source:
> Microsoft Data Transformation Services (DTS) Package Help file:
> sqldts80.hlp Help context: 1100 Error Detail Records: Error:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun:
> Package execution complete. Process Exit Code 1. The step failed.
> ************************************************** ***********

DTS

Hi folks,
I am running an Active-X in DTS package and it works perfect when execute
from the DTS design area but it fails when run as JOB. This is the error i
get. Any idea? I couldnt figure out the clue from this error msg. Any help
appreciated. TIA
***********************************************************
Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
string: The task reported failure on execution. Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 1100 Error Detail Records: Error:
-2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
reported failure on execution. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp Help
context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun:
Package execution complete. Process Exit Code 1. The step failed.
*************************************************************Either because it's now runnnig as user ABC\SYSTEM rather than as your login
or because all paths are now relative to the server (and using different
mapped drives) instead of relative to your workstation.
Those are the usual problems when executing on the server.
"rupart" wrote:
> Hi folks,
> I am running an Active-X in DTS package and it works perfect when execute
> from the DTS design area but it fails when run as JOB. This is the error i
> get. Any idea? I couldnt figure out the clue from this error msg. Any help
> appreciated. TIA
> ***********************************************************
> Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
> DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
> string: The task reported failure on execution. Error source:
> Microsoft Data Transformation Services (DTS) Package Help file:
> sqldts80.hlp Help context: 1100 Error Detail Records: Error:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun:
> Package execution complete. Process Exit Code 1. The step failed.
> *************************************************************

DTS

Hi folks,
I am running an Active-X in DTS package and it works perfect when execute
from the DTS design area but it fails when run as JOB. This is the error i
get. Any idea? I couldnt figure out the clue from this error msg. Any help
appreciated. TIA
****************************************
*******************
Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
string: The task reported failure on execution. Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 1100 Error Detail Records: Error:
-2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
reported failure on execution. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp Hel
p
context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
:
Package execution complete. Process Exit Code 1. The step failed.
****************************************
*********************Either because it's now runnnig as user ABC\SYSTEM rather than as your login
or because all paths are now relative to the server (and using different
mapped drives) instead of relative to your workstation.
Those are the usual problems when executing on the server.
"rupart" wrote:

> Hi folks,
> I am running an Active-X in DTS package and it works perfect when execute
> from the DTS design area but it fails when run as JOB. This is the error
i
> get. Any idea? I couldnt figure out the clue from this error msg. Any help
> appreciated. TIA
> ****************************************
*******************
> Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
> DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
> string: The task reported failure on execution. Error source:
> Microsoft Data Transformation Services (DTS) Package Help file:
> sqldts80.hlp Help context: 1100 Error Detail Records: Erro
r:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The tas
k
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp H
elp
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSR
un:
> Package execution complete. Process Exit Code 1. The step failed.
> ****************************************
*********************