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:
- Graphically, in DTS Designer, with the Transform Data task and the Data Driven Query task. Each Transform Data task or Data Driven Query task you add to a package contains one or more column transformations.
To configure the transformations, use the Transformations tab of the Transform Data Task Properties and Data Driven Query Properties dialog boxes. This tab graphically displays all the column mappings between the source and destination that are used in the task. You use this tab as a starting point for configuring the relationships between source and destination columns and the specific transformations to use.
- By using the default Copy Column transformation or by writing a Microsoft® ActiveX® script in the DTS Import/Export Wizard. The DTS Import/Export Wizard is limited to these two types of transformation. For more information, see Using ActiveX Scripts in DTS.
To modify a transformation script written and saved to a package created in the DTS Import/Export Wizard, you need to open the package in DTS Designer and edit the task associated with the script.
- Programmatically, using a Transform Data task, a Data Driven Query task, or a Parallel Data Pump task.
For more information, see DTS Transformations in Visual Basic and DTS Column Objects in Visual Basic.
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:
- One-to-one mappings, which contain a single source column and a single destination column.
- N-to-N mappings, which contain an equal number of multiple source and destination columns.
- Mappings with unequal numbers of source and destination columns.
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:
- Copy Column transformation
- Trim String transformation
- Date Time String transformation
- Middle of String transformation
- Read File transformation
- ActiveX Script transformation
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:
- Copy Column transformation
- Uppercase String transformation
- Lowercase String transformation
- ActiveX Script transformation
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:
- More source columns than destination columns
- More destination columns than source columns
- No source or destination columns
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