- Sql Server 2000 DTS
- Enabled Multi-phase datapump
- Dell Dual processor 1.2 GHZ w/ 1GB ram

Objective:
I am trying to do some error trapping in DTS so that if an error
occurrs, it will log the error and continue on. I am using an ActiveX
object to do the transformation.
I have set the "Max Error" to 9999 and the batch size to 1 so it will
post what it can and continue on. (Wonder if there is a better way to
this, it runs very slow....?). However, I would like a log of the
records that errored out.

As a test, I am writing a record to the table that I know will violate
the Primaary Key constraint. The error is logged to the specified
error file. However, it fails to generate the "source" and
"destination" files.

I then changed the "Maximum error" to 1 and kept the batch size at 1.
Re-ran the package expecting to see "OOPS!" in a dialog box, instead,
the package just terminated with the "standard" "Primary Key Violation"
dialog box. Shouldn't "On Insert Failure" method be triggered by the
error?

Thanks.

Jack


'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
DTSDestination("ord_number") = DTSSource("ord_number")
DTSDestination("ship_number") = DTSSource("ship_number")
DTSDestination("cust_code") = DTSSource("cust_code")
DTSDestination("inv_date") = DTSSource("inv_date")
Main = DTSTransformStat_OK
End Function

Function InsertFail()
MsgBox "OOPS!!!"
End Function

Hello jackso95 (AT) hotmail (DOT) com,

look at this from Alan

http://www.sqldts.com/default.aspx?282,5

it seems the right explanation about why your InsertFailureMain function
is not fired for PK violation.

By the way, this job is very slow; it would bebetter using a staging table
for loading all temporary data without costraints, then demanding to sql
server (using a stored procedure) the storicization of good data and the
garbaging of bad records.

Bye