DTS Programming
DTS Transformations in Visual Basic
A Data Transformation Services (DTS) transformation consists of a Transformation object and a class-specific transform server object, such as DataPumpTransformScript or DataPumpTransformTrimString. For more information about the transformations supplied with Microsoft® SQL Server™ 2000, see Transformation Objects.
Here are the basic steps for adding a Transformation object to a Microsoft Visual Basic® file:
- Declare an object variable of Transformation type and a transform server object variable of the appropriate type.
- Create the transformation with the New method of the Transformations collection of the class-specific task object or TransformationSet object. Pass the programmatic identifier (ProgID) of the transformation class to New as an argument.
- Use the TransformServer property of the Transformation object to return a reference to the transform server object.
- Assign a name unique among the objects in the Transformations collection to the Name property.
- Set other Transformation or transform server object properties as necessary.
- Add the Transformation object to the task with the Add method of the Transformations collection.
Example
The following code example shows you how to create a Copy Column transformation that does not have Column objects added. This transformation copies all columns. The source and destination must have the same number of columns, and this must be the only Transformation object in the Transformations collection.
'Declare the class-specific task and the transformation.
Dim objTransform As DTS.Transformation2
Dim objPumpTask As DTS.DataPumpTask2
. . .
'Create and add the transformation.
Set objTransform = objPumpTask.Transformations.New( _
"DTS.DataPumpTransformCopy")
objTransform.Name = "Transform"
objTransform.TransformFlags = _
DTSTransformFlag_AllowLosslessConversion
objPumpTask.Transformations.Add objTransform