DataPumpTransformMidString Object

The DataPumpTransformMidString object extracts a substring from the source column and converts it, if necessary, to the destination column data type. This object requires one source column and one destination column, both of a string data type (char, varchar, text, nchar, nvarchar, ntext, and flat file strings). The properties CharacterStart and CharacterCount specify the position of the substring.

Optionally, the transformation converts the extracted substring to uppercase or lowercase characters, as specified by the UpperCaseString and LowerCaseString properties. It also optionally trims white-space characters, as specified by the TrimLeadingWhiteSpace, TrimTrailingWhiteSpace, and TrimEmbeddedWhiteSpace properties. Substring extraction occurs before the trimming of white space characters.

Destination truncation is possible by setting DTSTransformFlag_AllowStringTruncation in the TransformFlags property of the Transformation2 object.

Properties

CharacterCount Property	TrimLeadingWhiteSpace Property
CharacterStart Property	TrimTrailingWhiteSpace Property
LowerCaseString Property	UpperCaseString Property
TrimEmbeddedWhiteSpace Property

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