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 2 Dealing with Datatype Conversion Errors.

To test for this we're going to have to make a change to the datatype of the state field in the authors2 table.

--Change a column datatype (Source = Char Dest = int)
ALTER TABLE authors2 ALTER COLUMN state INT

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

Figure 1.6 Error from datatype conversion.

DtypeConvNorm

Let's handle the error using phases and see what happens when we encounter that row.

Figure 1.7 Transform Failure.

DTTransPhases

Here we can see that we encountered an On Transform Error and in our function we can then handle it. The step as a whole returns success.

Here are some notes that you may find useful.

  • The Batch Complete phase is not available to you.
  • Source Data is not available to you in the Pre Source Phase or in the Post Source data and Pump Complete phases.
  • If you try to access the data in the Post Source data phase you will fail the step.
  • Data which is of a valid datatype 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.
Page 1 | Page 2 | Page 3 | Page 4 | Page 5