Data Transformation Services

Mapping Column Transformations

In Data Transformation Services (DTS), you can transfer data from a source rowset to a destination table by using one of the available DTS transformation types or by supplying a custom transformation. For some transformation types, the data is copied. In other cases, data is modified as transformations are applied or copied according to the rules of the transformation type. You can perform a column transformation in the following ways:

Mapping a Transformation in DTS Designer

You map a transformation in DTS Designer to establish the relationship between the source and destination columns. Mapping configurations can be of several types:

DTS allows you to create your own custom transformations, with their own column requirements, by programming objects that implement the IDTSDataPumpTransform interface. The mappings for these transformations can fall in one of the above categories or can have different requirements.

For more information, see Building a DTS Custom Transformation and IDTSDataPumpTransform (DTS).

One-to-One Column Mappings

You use one-to-one column mappings when the transformation requires one source and one destination column. By default, DTS Designer maps each source and destination column in a Transform Data task in this configuration, attempting to match each source and destination column by name (for example, CategoryName in the source would be mapped to CategoryName in the destination, and so forth). These are the types of transformations you use with one-to-one column mappings:

Transformations that use one-to-one column mappings are displayed with a single connecting arrow indicating the flow of data. The following diagram, from the Transformations tab of the Transform Data Task Properties dialog box, shows four such transformations (the bold arrow indicates that one of the transformations is selected).

N-to-N Column Mappings

Transformations using N-to-N column mappings require a matching number of multiple source and destination columns. You use this mapping in situations where each source column must have a corresponding destination column, and it is more efficient to configure all the transformations together (as a single data pump operation) rather than as separate transformations called individually for each row. By default, the Data Driven Query task uses this type of mapping configuration.

N-to-N column mappings include the following types of transformations:

N-to-N column mappings are shown with a single arrow connecting an equal number of branches at each end. The following diagram shows a mapping for this transformation that connects four source and four destination columns:

If you edit a Copy Column transformation so that the same source column is copied to multiple destination columns, the mapping will change to indicate the data flow, and the number of mapping lines touching the source and destination tables will be unequal. This type of mapping indicates a single source column is being copied to multiple destination columns.

Note  A single many-to-many Copy Column transformation is faster then many one-to-one Copy Column transformations. For more information, see Enhancing Performance of DTS Packages.

Mappings with Unequal Numbers of Source and Destination Columns

A transformation mapping can include an unequal number of source and destination columns. For example:

Following are several examples of these types of mappings.

One or More Source Columns and No Destination Columns

You can have an ActiveX Script transformation where only the values from a source table are processed. In the following example, written in Microsoft Visual Basic® Scripting Edition (VBScript), each row of the CategoryName column (from the Categories table of the Northwind sample database) is checked for the presence of a NULL value or a null string. If neither of those values is found for the row, a package global variable is assigned the value of CategoryName.

Following is the sample ActiveX transformation script for this type of mapping:

Function Main()
   If Not IsNull (DTSSource("CategoryName")) Then
      If LEN(DTSSource("CategoryName")) > 0 Then
         DTSGlobalVariables("gv2") = DTSSource("CategoryName")
      End If
   End If
   Main = DTSTransformStat_OK
End Function

Although this example uses only one source column, you also can create ActiveX Script transformations using multiple source columns and no destination columns.

Two Source Columns and No Destination Column

The Write File column transformation is an example of a transformation with a specialized mapping requirement. The transformation takes data from one source column and writes it to a file, the name of which it finds in a second source column. As a result, it requires two source columns (one column containing the data to be copied and a second column containing a list of file names) and zero destination columns.

In the following diagram, Write File column transformations originate from the source table and do not touch any destination columns.

One or More Destination Columns and No Source Columns

You can have an ActiveX Script transformation where only the values from a destination table are processed. In the example below, an incrementing counter value is appended to the value of two global variables, and the concatenated strings are assigned to the CategoryName and Description columns of the Categories table.

Following is the sample ActiveX transformation script for this type of mapping:

Dim N

Function Main()

   If IsEmpty(N) Then
      N = 0
   End If

   DTSDestination("CategoryName") = DTSGlobalVariables("gv1") & (N)
   DTSDestination("Description") = DTSGlobalVariables("gv2") & (N)
   N = N + 1   
Main = DTSTransformStat_OK

End Function
No Destination Columns and No Source Columns

There may be cases where the ActiveX Script transformation does not reference any source or destination columns. For example, the script may only involve the processing of global variables or lookup queries, or an action such as a notification.

Following is an example of a simple ActiveX transformation script for this type of mapping. In the following script, the value of a global variable is incremented for each row of data in the source:

Dim counter

Function Main()
   Counter = counter + 1
   DTSGlobalVariables("gv1").Value = DTSGlobalVariables("gv1") + 1
   Main = DTSTransformStat_SkipInsert
End Function

The following script could be used to skip further inserts after the required items have been loaded in a transformation:

Function Main()
   If DTSGlobalVariables("LoadComplete").Value  = True Then 
      Main = DTSTransformStat_SkipRow
   Else
      Main = DTSTransformStat_OK
   End if
End Function

See Also

Data Driven Query Task

ParallelDataPump Task Object

Transform Data Task

Using Global Variables With DTS Packages

 D="EndOfFile">