Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Unanswered: Handle DTS Errors

    Hi,
    I'm using DTS for pulling data from Oracle into SQL Server.

    I retrieve the records from Oracle and insert into a staging table and from there invoke an ExecuteTask in DTS to transfer the data to the SQL Server table deciding on whether to Insert / Update / Delete appropriately based on certain parameters.

    During the Insert / Update / Delete there might be constraints on the original SQL Server table which might throw errors in case of data inconsistencies. I want to track the errors arising in this scenario and take a call on whether to ignore them or not.

    When I schedule the DTS package from the designer the constraint violations are picked up by the DTS and it stops the package execution. Later I coded the same on VBScript and I've handled the error using the WithEvents along with the Package2 object and tried giving a Cancel = False in the OnError Event.

    But this does not seem to be working. The package is still exiting abnormally. The piece of code which I've used to Cancel the DTS Error is given below.

    Private Sub goPackage_OnError(ByVal EventSource As String, _
    ByVal ErrorCode As Long, _
    ByVal Source As String, _
    ByVal Description As String, _
    ByVal HelpFile As String, _
    ByVal HelpContext As Long, _
    ByVal IDofInterfaceWithError As String, _
    ByRef pbCancel As Boolean)

    Debug.Print "DTSPackage - Error"
    pbCancel = False
    End Sub

    Private Sub goPackage_OnQueryCancel(ByVal EventSource As String, _
    ByRef pbCancel As Boolean)

    Debug.Print "DTSPackage - Query cancelled"
    pbCancel = False
    End Sub

    Setting pbCancel = False should ideally inform the DTS package to continue with execution and ignore the Errors but then the package is still terminating abnormally when an error is encountered and the rest of the records do not get processed.

    Any suggestions in this regard would be greatly appreciated.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Handle DTS Errors

    A couple of thoughts:

    1. Your code includes the statements Debug.print. I don't think that Debug.print is supported in VBScript and it's not supported at runtime in VB.

    2. Have you looked at DTSTransformStat_SkipRow? I'm having a hard time picturing where this code is in your DTS Package. However, if you use the ActiveX script to copy data over, you can specify that on an error the row should be skipped (and you can log that row if you wanted). For example:

    Code:
    Function Main
    
      DTSDestination("Foo") = DTSSource("Foo")
      DTSDestination("Bar") = DTSSource("Bar")
    
      errCount = 0
    
      ' Do your exception handling here
      If DTSSource("Foo") > 10 Then 
        errCount = errCount + 1
      End if
    
      If DTSSource("Bar") < 8 Then
        errCount = errCount + 1
      End if
    
      If errCnt > 0 Then
        Main = DTSTransformStat_SkipRow
      Else
        Main = DTSTransformStat_OK
      End If
    End function
    Note that this is probably a very sloppy (not to mention slow) way of doing things. Nevertheless, it is a potential solution to your question.

    Hopefully, it will give you some ideas that you can pursue.


    Regards,

    Hugh Scott

    Originally posted by Mohana Krishnan
    Hi,
    I'm using DTS for pulling data from Oracle into SQL Server.

    I retrieve the records from Oracle and insert into a staging table and from there invoke an ExecuteTask in DTS to transfer the data to the SQL Server table deciding on whether to Insert / Update / Delete appropriately based on certain parameters.

    During the Insert / Update / Delete there might be constraints on the original SQL Server table which might throw errors in case of data inconsistencies. I want to track the errors arising in this scenario and take a call on whether to ignore them or not.

    When I schedule the DTS package from the designer the constraint violations are picked up by the DTS and it stops the package execution. Later I coded the same on VBScript and I've handled the error using the WithEvents along with the Package2 object and tried giving a Cancel = False in the OnError Event.

    But this does not seem to be working. The package is still exiting abnormally. The piece of code which I've used to Cancel the DTS Error is given below.

    Private Sub goPackage_OnError(ByVal EventSource As String, _
    ByVal ErrorCode As Long, _
    ByVal Source As String, _
    ByVal Description As String, _
    ByVal HelpFile As String, _
    ByVal HelpContext As Long, _
    ByVal IDofInterfaceWithError As String, _
    ByRef pbCancel As Boolean)

    Debug.Print "DTSPackage - Error"
    pbCancel = False
    End Sub

    Private Sub goPackage_OnQueryCancel(ByVal EventSource As String, _
    ByRef pbCancel As Boolean)

    Debug.Print "DTSPackage - Query cancelled"
    pbCancel = False
    End Sub

    Setting pbCancel = False should ideally inform the DTS package to continue with execution and ignore the Errors but then the package is still terminating abnormally when an error is encountered and the rest of the records do not get processed.

    Any suggestions in this regard would be greatly appreciated.

  3. #3
    Join Date
    Mar 2003
    Posts
    2
    Thanks buddy. This did give me a idea. I split my tables into Staging tables and Temporary tables. Finally added a task in between to make sure that all the erroneous records are filtered out and moved to the respective error tables. I wanted to check for foreign key violations mostly. Later I went ahead and inserted the rest of the records in the staging tables directly into the master tables. This worked out fine.

    Thanks anyway.
    Regards
    Mohan

Posting Permissions

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