Column Mappings

The DTS Import and DTS Export wizards allow you to map a source column to a destination column as the data is copied.

You can stipulate how a source is mapped to a destination by:

  • Specifying which source columns to copy.
  • Specifying the destination column where the data will be copied.
  • Changing the data type of the data, if a valid data conversion is applicable.

For example, you could copy the ord_date column of data type smalldatetime to a destination column new_ord_date with a data type of datetime. Alternatively, all the columns in the sales table except the payterms column could be copied to a new table.

If you create a new table with more columns than exist at the source, you cannot map to all of the destination table columns in the wizard. You are limited to the number of columns that exist in the source table. Use DTS Designer for this operation.

You can determine what happens to existing data, if any, in the destination table before the source data is copied.

Use this | To do this
Create destination table | Create the destination table before copying source data (default if the table does not exist). If the destination table exists, an error may occur, unless Drop and recreate destination table if it exists is selected.
Delete rows in destination table | Delete all rows in the destination table before copying source data. This option is only available if the destination table already exists. Indexes and constraints on the destination table remain.
Append rows to destination table | Insert source data into the destination table (default). All existing data, indexes, and constraints on the destination table remain. Rows are not necessarily appended to the end of the destination table. Only by using a clustered index on the destination table can you determine where rows are inserted. This option is only available if the destination table already exists.
Edit SQL | Modify the default CREATE TABLE statement generated by the DTS wizard.
Drop and recreate destination table | Drop the destination table if it exists and re-create it before attempting to copy data into it. All existing data in the destination table and any indexes are permanently deleted.
Enable identity insert | Allow explicit values to be inserted into the identity column of a table (SQL Server only). This option is only available if an identity column is detected.

For each column of source data under Mappings, these properties must be specified.

Use this | To do this
Source | Choose the column name in the source table to copy to Destination. Choose <ignore> if the source data should not be copied. If Destination is defined as NOT NULL, then entering <ignore> can result in an error if there is no DEFAULT value for Destination.
Destination | Enter the column name in the destination table where the data from Source should be inserted. If the destination table is being created, a new column name can be entered. Enter <ignore> if the destination column should not be created. If <ignore> is chosen and the destination column already exists, Source is not copied for this column.
Type | Enter the data type for each Destination column. Only a valid data conversion should be specified. For example, while it is possible to convert an int to a tinyint, without potential data truncation, the operation will fail if the conversion would result in data truncation.
Nullable | Select if Destination can allow null values.
Size | Enter the length in units corresponding to the data type of the Destination column. This value is only applicable for the char, varchar, nchar, nvarchar, binary, and varbinary data types. A size smaller than the length of Source can result in data truncation.
Precision | Enter the maximum number of decimal digits that can be stored, to the left and to the right of the decimal point. Applies to decimal and numeric data types only.
Scale | Enter the maximum number of decimal digits that can be stored to the right of the decimal point. Must be less than or equal to Precision. Applies to decimal and numeric data types only.

The default mapping specifies that:

  • Every column in the source is copied.
  • Source data maps to the same column name, data type, nullability, size, precision, and scale in the destination table.
  • Source data is appended to the destination table. If the destination table does not exist, it is created.


Scripting a Data Transformation

The DTS Import and DTS Export wizards allow you to specify how the source data is transformed when copied to the destination table. You can:

  • Copy source columns to destination columns without making any changes.
  • Use Microsoft® JScript®, Microsoft Visual Basic® Scripting Edition or Microsoft ActiveX® scripting engines to code the transformation of each source column as it is copied to the destination. DTS enumerates the list of available scripting engines automatically based on those installed on your computer.
  • Load a previously written VBScript or JScript script that describes how each source column is transformed as it is copied to the destination.
Use this | To do this
Copy the source columns directly to the destination columns | Copy the source columns to the destination columns without changing the data. No transformation code is executed.
Advanced | Set transformation flags, which define how data is validated and converted before it is copied from the source to the destination.
Transform information as it is copied to the destination | Specify that each source column should be transformed, based on the VBScript or JScript provided, as it is copied to the destination.
Language | Choose the scripting language used to write the transformation code. Click Java Script or VBScript.
Browse | Find and load a file containing previously written transformation code.

Examples of possible transformations include:

  • Modifying all character columns in the source so that they are uppercase in the destination.
  • Combining two source columns and inserting them into one destination column. For example, appending a surname column to a first_name column and inserting the result into a single name column in the destination.
  • Splitting a source column and inserting the values into multiple destination columns. For example, splitting a single date column into month, day, and year values.
  • Updating a running total for one or more columns.
  • Validating each column. Invalid rows can be skipped.
Scripting

A transformation script can perform any operation supported by the JScript, or VBScript languages. DTS exposes three collections for use with the DTS Import and DTS Export wizards:

  • DTSDestination
  • DTSSource
  • DTSGlobalVariables

The DTSDestination and DTSSource collections each have one property and one method.

Use this | To do this
Count property | Specify the number of columns in the object. DTSDestination.Count specifies the number of columns at the destination. DTSSource.Count specifies the number of columns at the source.
Item method | Specify the column in the object by name or ordinal (position). Used to set or get the column value. For example: DTSDestination(3) specifies the third column at the destination. DTSSource(“au_lname”) specifies the au_lname column at the source.

The values of the source columns are copied to the DTSSource collection. The value of any source column can be used to calculate and assign a new value to the destination column. The ActiveX transformation script places the transformed value into the DTSDestination column variants, which in turn are placed by the DTSTransformScript server into the memory allocated by the DTS Data Pump for the row.

Note Only columns included in mappings are available for use in a script.

In addition to the Count property and the Item method, DTSSource and DTSDestination also support these ADO field properties:

  • ActualSize
  • Attributes
  • DefinedSize
  • Name
  • NumericScale
  • OriginalValue
  • Precision
  • Recordset
  • Type
  • UnderlyingValue
  • Value

The DTSLookups collection, which is used in transformations that reference lookup tables, is not available using the DTS Import and DTS Export wizards, but is available using the DTS Designer.

Note ActiveX scripts can also access and manipulate data using Active Data Objects (ADO) or invoke any object that supports OLE Automation. A script can load and execute an external program (.exe), or even a batch file (.cmd or .bat). Transformation scripts can also retrieve and execute other DTS packages.

Transformation Return Codes

Transformation return codes indicate to the calling application, for example the DTS Import Wizard, whether the transformation of the row completed successfully or if any further action needs to be taken by the calling application. The value of the transformation return code is determined by setting these constants.

Constant | Description
DTSTransformStat_OK | Transformation succeeded. Write the row to the destination if specified. Do not call any error handlers. This is the default return code.
DTSTransformStat_SkipRow | Terminate further processing of this row. Do not call any error handlers.
DTSTransformStat_SkipFetch | Do not fetch the next row. Reexecute the transformation against the current source and destination rows. No initialization is done to the destination row. This could be used to generate multiple output rows from a single input row. Subsequent transformations are still executed unless DTSTransformStat_SkipRow is also set.
DTSTransformStat_SkipInsert | Do not write the current row to the destination. Usually used to fetch the next source row to generate a simple aggregate. Subsequent transformations are still executed unless DTSTransformStat_SkipRow is set.
DTSTransformStat_Info | Success with additional information. The application may process further by reading its pvTransformUserData (if it shares that knowledge with the transformation server) or through OLE DB error records. The DTS Data Pump calls the ErrorSink if one is specified, but does not increment the error row count. Set the appropriate status bit the application requires to pass to the ErrorSink to process for more information. Use DTSTransformStat_Error to indicate error. Do not use DTSTransformStat_Info.
DTSTransformStat_OKInfo | Combination of DTSTransformStat_OK and DTSTransformStat_Info. Write row to destination if specified, and call ErrorSink with DTSTransformStat_Info.
DTSTransformStat_SkipRowInfo | Combination of DTSTransformStat_SkipRow and DTSTransformStat_Info. Abort further processing of this row, and call ErrorSink with DTSTransformStat_Info.
DTSTransformStat_Error | Error has occurred. Pass the status bit to the ErrorSink, and process as an error.
DTSTransformStat_ErrorSkipRow | Terminate further processing of this row due to an error and call the ErrorSink to handle the error. Do not write the row to an exception file.
DTSTransformStat_AbortPump | Bit-wise OR with other status bits to abort processing any further rows.
DTSTransformStat_NoMoreRows | The end of the record set has been reached.

Note Use the constant rather than the decimal or hexadecimal value of the transformation return code in the script.

The transformation return codes must be assigned to the name of the JScript or VBScript transformation function before the function exits.

This is a Visual Basic transformation script sample.

'**********************************************************************

'    Visual Basic Transformation Script

'    Copy each source column to the

'    destination column and perform a transformation

'**********************************************************************

Function Transform()

'************ Declare Some Variables

Dim strPhone

Dim StrNew

Dim cChar

Dim iPhoneLen

Dim x

Dim y

'************ Copy Source to Destination

DTSDestination("CUSTOMERID") = DTSSource("CUSTOMERID")

DTSDestination("COMPANYNAME") = DTSSource("COMPANYNAME")

DTSDestination("CONTACTFIRSTNAME") = DTSSource("CONTACTFIRSTNAME")

DTSDestination("CONTACTLASTNAME") = DTSSource("CONTACTLASTNAME")

DTSDestination("BILLINGADDRESS") = DTSSource("BILLINGADDRESS")

DTSDestination("CITY") = DTSSource("CITY")

DTSDestination("POSTALCODE") = DTSSource("POSTALCODE")

DTSDestination("CONTACTTITLE") = DTSSource("CONTACTTITLE")

DTSDestination("FAXNUMBER") = DTSSource("FAXNUMBER")

'************ Call Any Function Or COM Object

DTSDestination("STATEORPROVINCE") = ucase(DTSSource("STATEORPROVINCE"))

DTSDestination("COUNTRY") = ucase(DTSSource("COUNTRY"))

'************ Scrub, Validate, and Calculate Missing Values

strPhone = DTSSource("PHONENUMBER")

y = Len(strPhone)

x = 1

While x <= y

    cChar = Mid(strPhone, x, 1)

    If (cChar >= "0" And cChar <= "9") Then

        StrNew = StrNew & cChar

    End If

    x = x + 1

Wend

'************ Validate and Format Data

If (Len(StrNew) <> 10) Then

    Transform = DTSTransformStat_Error

    Exit Function

Else

    DTSDestination("PHONENUMBER") = "(" + Mid(StrNew, 1, 3) + ") " + _

    Mid(StrNew, 4, 3) + "-" + Mid(StrNew, 7, 4)

End If

Transform = DTSTransformStat_OK

End Function