Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Multiphase Data Pump (Field Notes)
By Allan Mitchell
Version 2000
Level Beginner
Page 1 | Page 2 | Page 3 | Page 4 | Page 5

Problem 3 Dealing with Primary Key Violation Errors.

To test for this we're going to have to add a Primary Key Constraint the authors2 table.

--Change the Primary Key
ALTER TABLE authors2 ADD CONSTRAINT pk_bit PRIMARY KEY (contract)

If we were to use a data pump with no phases then we'd get the error returned in Figure 1.8.

Figure 1.8 Error from Primary Key Violation.

PKViolNorm

The error returned and handled when we use phases is the same as that found in Figure 1.7

Here are some notes that you may find useful.

  • The Batch Complete and the Post Data phases are not available to you.
  • Source Data is not available to you in the Pump Complete phases.
  • If you try to access the data in the Post Source data phase you will fail the step.
  • Data which does not break the constraint is entered into the destination.
  • You must handle the On Insert Failure when you have an On Transform Failure. DTS will convert the row to NULL so any non-nullable columns will error.
  • If you have the data pump set to FAST LOAD then even when you encounter a Primary Key Violation the On Insert Success phase is called. It is only when we end the data pump that the failure occurs. If you want to handle a primary key violation then you need to turn off FAST LOAD and then the correct phases are called so you can handle the error.
Page 1 | Page 2 | Page 3 | Page 4 | Page 5