DTS Programming

Remarks

If only case conversion is required, the DataPumpTransformLowerString or DataPumpTransformUpperString objects can be used. These transformations accept multiple source and destination columns. If only case conversion and trimming of white space is required, the DataPumpTransformTrimString object can be used.

The New method of the Transformations collection of the DataPumpTask, DataDrivenQueryTask, and TransformationSet objects returns a reference to a Transformation object. The TransformServer property of the Transformation object returns a reference to the appropriate custom transformation object.

Example

This example Microsoft® Visual Basic® program splits the phone column from the authors table in the pubs database into two columns named AreaCode and LocalPhone while copying it to a Microsoft Excel worksheet named Phones in PubsAuthors.xls. The worksheet had been previously created by the Microsoft OLE DB Provider for Jet from:

CREATE TABLE `Phones` (
`AreaCode` VarChar (255) ,
`LocalPhone` VarChar (255) )

The basic steps for manually creating a worksheet are as follows:

1. Open a new workbook in Excel and rename one of the blank sheets Phones.
   
   
2. Enter AreaCode in cell A1 and LocalNumber in cell B1.
   
   
3. Save the workbook as PubsAuthors.xls, and then close Excel.

Public Sub Main()
'Copy pubs..authors.phone to Excel. Split out area code and local number.
    Dim oPackage    As DTS.Package
    Dim oConnect    As DTS.Connection
    Dim oStep       As DTS.Step
    Dim oTask       As DTS.Task
    Dim oCustTask   As DTS.DataPumpTask
    Dim oTransform  As DTS.Transformation
    Dim oColumn     As DTS.Column
    Dim oMidString  As DTSPump.DataPumpTransformMidString

    Set oPackage = New DTS.Package
    oPackage.FailOnError = True
    
    'Establish a connection to the source server.
    Set oConnect = oPackage.Connections.New("SQLOLEDB.1")
    With oConnect
        .ID = 1
        .DataSource = "(local)"
        .UseTrustedConnection = True
    End With
    oPackage.Connections.Add oConnect
    Set oConnect = Nothing

    'Establish a connection to an Excel worksheet.
    Set oConnect = oPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")
    With oConnect
        .ID = 2
        .DataSource = "D:\DTS_UE\Data\PubsAuthors.xls"
        .ConnectionProperties("Extended Properties").Value = _
                                            "Excel 8.0;HDR=YES;"
    End With
    oPackage.Connections.Add oConnect
    Set oConnect = Nothing

    'Create a step and task, and then link the step to the task.
    Set oStep = oPackage.Steps.New
    oStep.Name = "MidStringStep"
    Set oTask = oPackage.Tasks.New("DTSDataPumpTask")
    Set oCustTask = oTask.CustomTask
    oCustTask.Name = "MidStringTask"
    oStep.TaskName = oCustTask.Name
    oPackage.Steps.Add oStep
    
    'Link the task to the connections.
    With oCustTask
        .SourceConnectionID = 1
        .SourceObjectName = "[pubs].[dbo].[authors]"
        .DestinationConnectionID = 2
        .DestinationObjectName = "Phones$"
    End With
    
    'Create an area code transform, and link it to source and destination columns.
    Set oTransform = oCustTask.Transformations. _
            New("DTSPump.DataPumpTransformMidString")
    With oTransform
        .Name = "AreaCodeTransform"
        .SourceColumns.AddColumn "phone", 1
        .DestinationColumns.AddColumn "AreaCode", 1
    End With
            
    'Define start and width for area code, and then link transform to task.
    Set oMidString = oTransform.TransformServer
    oMidString.CharacterStart = 1
    oMidString.CharacterCount = 3
    oCustTask.Transformations.Add oTransform
    
    'Create local numeric transform, and then link to source and destination columns.
    Set oTransform = oCustTask.Transformations. _
            New("DTSPump.DataPumpTransformMidString")
    With oTransform
        .Name = "LocalNumTransform"
        .SourceColumns.AddColumn "phone", 1
        .DestinationColumns.AddColumn "LocalNumber", 1
    End With
            
    'Define start and width for local number.
    Set oMidString = oTransform.TransformServer
    oMidString.CharacterStart = 5
    oMidString.CharacterCount = 8

    'Link transform to task and task to package. Then run the package.
    oCustTask.Transformations.Add oTransform
    oPackage.Tasks.Add oTask
    oPackage.Execute
End Sub

See Also

Adding DTS Column Objects

Adding DTS Transformations

DataDrivenQueryTask2 Object

DataPumpTask2 Object

DataPumpTransformLowerString Object

DataPumpTransformTrimString Object

DataPumpTransformUpperString Object

New (ID) Method

Transformation2 Object

Transformations Collection

TransformationSet Object

TransformFlags Property

TransformServer Property