Column Mapping

Column mapping defines the relationships between source and destination columns. Transformations belong to one of these categories.

Transformation type	Action
Column copy	Copy data from the source column(s) to the destination column(s).
ActiveX script	Use an ActiveX script to move the data or reset global variables.
Custom transformation	Use a custom transformation registered on your computer, which a developer may have added to define special transformation operations.

When creating a mapping, choose a transformation type. Then, map source and destination columns either to each other or to a global variable.

Graphical Display of Transformations

The Transformations tab of the Transform Data task uses several devices to depict column mappings graphically:

Source table and Destination table column names are displayed in separate lists.
Arrows are used to represent the mappings between source and destination columns.
DTS Designer initially matches columns in the source table to columns in the destination table using a copy column transformation. If the Transform Data task was created in DTS Designer, the default mappings will consist of all possible single mappings between source and destination columns. For a Data Driven Query task, the default mapping consists of a many-to-many mapping using an ActiveX script transformation.

Mapping Configurations

DTS Designer allows several possible mapping configurations.

Mapping configuration	Transformation type	Action	Shown by
Single	Copy Column, ActiveX Script, or Custom Transformation	Maps a single source column to a single destination column.	Unidirectional arrow from source to destination column.
Many-to-one	ActiveX Script or Custom Transformation	Maps several source columns to a single destination column.	Lines from selected source columns merge and form an arrow that points to the destination column.
One-to-many	ActiveX Script or Custom Transformation	Maps a source column to multiple destination columns.	Line from a source column forms multiple arrows that point to destination columns.
Many-to-many	Copy Column, ActiveX Script, or Custom Transformation	Maps several source columns to several destination columns. Use whenever possible, as fewer transformations are invoked with this option than with multiple single column mappings.	Lines from multiple source columns merge and form arrows that point to multiple destination columns.
Global variable	ActiveX Script or Custom Transformation	Maps one or more source or destination columns to an ActiveX script using one or more global variables. For source columns, set the return value in the script to DTSTransformStat_ NoMoreRows or DTSTransformStat_ SkipRow. For destination columns, set the return value in the script to skip fetch.	Curved line: Starting from source column(s) but not ending on destination columns Or Ending on destination column(s) but not starting from source columns.

Column Mapping Properties

By right-clicking an individual mapping line, you can:

Choose which transformation flags to use.
Allows you to define data conversions precisely between individual source and destination columns.
Test the transformation.
Allows you to execute the transformation on a sample of data and observe the results, without affecting the destination table.
Set column properties.
If a copy column transformation is selected, you can remap source-to-destination columns.
Set ActiveX script properties.
If an ActiveX script is selected, you can write a script for a mapping or reconfigure an existing one. In addition to performing transformations on a source to destination mapping, you can use the script to generate the source or destination data, or set one or more variables, triggers, or events.

Note Double-clicking a mapping line is a quick method of displaying its properties.

How to Define the Column Mappings

Before mapping source-to-destination columns, setting global variables, and defining transformations, you first need to define on the DTS Designer work space:

A source connection and a destination connection.
A Transform Data task pointing from the source connection to the destination connection.

You then need to define the source table and the destination table, using the Source and Destination tabs of the Data Transformation Properties dialog box.

To copy a single source column to a single destination column

To copy multiple source columns to an equal number of destination columns

To copy one or more source columns to an unequal number of destination columns

To map one or more source or destination columns to a global variable