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.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:

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.|||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