The DTS Import and DTS Export wizards allow you to specify transformation flags that define how data can be converted validly between the source and destination. The data transformation process involves the DTS Data Pump checking initially to see if the copy operation is possible. The transformation flags determine if the requirements the DTS Data Pump must check apply between corresponding columns in the source and destination. If any columns fail the check, then the copy operation fails the validation step, and no data is copied.
For example, the copy operation may require an exact match of data type, size, and nullability between all source and destination columns before the copy proceeds.
Data conversion can be performed at different levels.
| Use this | To do this |
|---|---|
| Default Transformation Flags - all possible conversions are allowed | Specify that only safe data type conversions (those that do not allow loss of data) are permitted (default):
Allow data type promotion |
| Require exact match between source and destination |
Specify an exact match between the source and destination. Before the copy can proceed, the data type, size, precision, scale, and nullability of the source and destination must be identical. This is the strictest requirement level. |
| Custom Transformation Flags | Allow any of the following data type transformations: promotion, demotion, and NULL conversion. |
| Allow data type promotion | Specify that shorter numeric data types can be converted to wider variants of the same data type, where applicable. This allows, for example, 16-bit numbers to be converted to 32-bit numbers. Examples include:
smallint to int |
| Allow data type demotion | Specify that wider numeric data types can be converted to shorter variants of the same data type, where applicable. This allows, for example, 32-bit numbers to be converted to 16-bit numbers. Examples include:
int to smallint Converting a number from 32-bit to 16-bit may result in an overflow error. |
| Allow NULL conversion | Specify that source columns not permitting null values can be copied to destination columns permitting null values. |
Note These are advanced properties and should be used carefully.