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.
Let's handle the error using phases and see what happens when we encounter that row.
Figure 1.7 Transform Failure.
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.