DTS Column Objects in Visual Basic
You must specify the columns to be transformed when:
- There are multiple Transformation objects in the Transformations collection.
- The number of source and destination columns is different (for example, if you are not transforming all columns).
- The order of the source and destination columns does not match.
Here are the basic steps for adding Column objects to a transformation in Microsoft® Visual Basic®:
- Create each Column object with the New method of the SourceColumns or DestinationColumns collections of the Transformation object.
- Specify the column name and ordinal position as arguments to New.
- Set the properties of the Column object as appropriate.
- Use the Add method of the SourceColumns or DestinationColumns collection to add the Column object to the appropriate collection.
Example
The following example shows you how to create and include one source and one destination Column object and to use the transform server object to set class-specific properties:
'Declare the class-specific task, the transformation, the column, and the transform server object.
Dim objDataPump As DTS.DataPumpTask
Dim objTransform As DTS.Transformation
Dim objColumn As DTS.Column
Dim objMidString As DTSPump.DataPumpTransformMidString
. . .
'Create an area code transformation.
' create columns, define start and width
Set objTransform = objDataPump.Transformations. _
New("DTSPump.DataPumpTransformMidString")
With objTransform
.Name = "AreaCodeTransform"
Set objColumn = .SourceColumns.New("phone", 1)
.SourceColumns.Add objColumn
Set objColumn = .DestinationColumns.New("AreaCode", 1)
.DestinationColumns.Add objColumn
End With
Set objMidString = objTransform.TransformServer
objMidString.CharacterStart = 1
objMidString.CharacterCount = 3
objDataPump.Transformations.Add objTransform
Using the AddColumn Method
If it is not necessary to set Column object properties, you can use the AddColumn method of the SourceColumns and DestinationColumns collections to create the column object and add it to the appropriate collection in a single step. However, AddColumn does not return a reference to the Column object. Usually, it is not necessary to set Column object properties like DataType, Nullable, Precision and Size, because the defaults are set from the corresponding column in the data source or destination.
Example
The following code example shows you how to use AddColumn to create and add the Column objects. The declarations from the previous example apply to this one, as well.
'Create a transformation for the local phone number. Create the columns, define the field start and width
Set objTransform = objDataPump.Transformations. _
New("DTSPump.DataPumpTransformMidString")
With objTransform
.Name = "LocalNumTransform"
.SourceColumns.AddColumn "phone", 1
.DestinationColumns.AddColumn "LocalNumber", 1
End With
Set objMidString = objTransform.TransformServer
objMidString.CharacterStart = 5
objMidString.CharacterCount = 8
objDataPump.Transformations.Add objTransform
If you need to reference a column property when you do not have a reference to the Column object, you can do it through the collection that contains it. The following code example shows you how to set the Nullable property of the phone column from the previous example:
objTransform.SourceColumns("phone").Nullable = True
After the Transformation object has been added to the Transformations collection, the object variables are no longer needed and can be reused for other objects or set to Nothing to release their references.