Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    4

    Question Unanswered: 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 2000

  2. #2
    Join Date
    Aug 2003
    Location
    Boston, MA
    Posts
    7
    You 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •