February 14, 2008 at 3:57 pm
SQL Server 2000
I have created a small subset of records to manage Primary Key violation during a multiphase data pump. Every record in the source subset will fail the primary key contraint in the destination data. I am doing this to automated sending of an email when the pump fails.
The problem I am having is that the Insert Success phase is being called regardless of whether or not the insert is successful. On the other hand, the Insert failure is not being called as expected.
Only at the end of the Data pump does the violation message appear. I have done some research of when these phases are called and cannot explain the behaviour.
The Activex Script for the data pump is listed below.
Thanks.
Function Main()
DTSDestination("org_unit_id") = DTSSource("org_unit_id")
DTSDestination("Start_Range") = DTSSource("Start_Range")
DTSDestination("End_Range") = DTSSource("End_Range")
DTSDestination("New_Referrals") = DTSSource("New_Referrals")
DTSDestination("New_Clients_Seen") = DTSSource("New_Clients_Seen")
Main = DTSTransformStat_OK
End Function
Function PreSourceMain()
PreSourceMain = DTSTransformstat_OK
End Function
Function InsertSuccessMain()
msgbox "Insert Success - made it"
InsertSuccessMain = DTSTransformstat_OK
End Function
Function InsertFailureMain()
msgbox "Insert Failure - made it"
InsertFailureMain = DTSTransformstat_OK
End Function
Function PumpCompleteMain()
PumpCompleteMain = DTSTransformstat_OK
End Function
🙂 What gets us into trouble is not what we don't know, it's what we know for sure that just ain't so....Mark Twain
February 14, 2008 at 4:54 pm
The InsertFailureMain function isn't being called because you have the Fast Load option enabled.
Go to the Options tab of the transformation task properties window, and uncheck 'Use Fast Load' under the SQL Server section.
February 17, 2008 at 2:51 pm
Thankyou SSC. It now works.
🙂 What gets us into trouble is not what we don't know, it's what we know for sure that just ain't so....Mark Twain
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply