DataPumpTransformLowerString Object

The DataPumpTransformLowerString object converts a source column to lowercase characters and, if necessary, to the destination column data type. It requires source and destination columns to be of string data types (char, varchar, text, nchar, nvarchar, ntext, and flat file strings). Like the DataPumpTransformCopy object, this transformation object supports multiple source and destination columns. Destination truncation is possible by setting DTSTransformFlag_AllowStringTruncation in the TransformFlags property of the Transformation2 object. There are no custom transformation properties.

Remarks

Conversion to lowercase characters is also a feature of the DataPumpTransformTrimString and DataPumpTransformMidString objects.

Example

This example Microsoft® Visual Basic® program converts two columns from the authors table in the pubs database to lowercase characters while copying them to a table named AuthNames in a database named DTS_UE.

Public Sub Main()
'copy pubs..authors names to DTS_UE..AuthNames, making lower case
    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
    
    Set oPackage = New DTS.Package
    oPackage.FailOnError = True
    
    'establish connection to source server
    Set oConnect = oPackage.Connections.New("SQLOLEDB.1")
    With oConnect
        .ID = 1
        .DataSource = "(local)"
        .UseTrustedConnection = True
    End With
    oPackage.Connections.Add oConnect

    'establish connection to destination server
    Set oConnect = oPackage.Connections.New("SQLOLEDB.1")
    With oConnect
        .ID = 2
        .DataSource = "(local)"
        .UseTrustedConnection = True
    End With
    oPackage.Connections.Add oConnect

    'create step and task, link step to task
    Set oStep = oPackage.Steps.New
    oStep.Name = "LowerCaseStep"
    Set oTask = oPackage.Tasks.New("DTSDataPumpTask")
    Set oCustTask = oTask.CustomTask
    oCustTask.Name = "LowerCaseTask"
    oStep.TaskName = oCustTask.Name
    oPackage.Steps.Add oStep
    
    'link task to connections
    With oCustTask
        .SourceConnectionID = 1
        .SourceObjectName = "pubs..authors"
        .DestinationConnectionID = 2
        .DestinationObjectName = "[DTS_UE].[dbo].[AuthNames]"
    End With
    
    'create custom transform, link to source and dest columns
    Set oTransform = oCustTask.Transformations. _
            New("DTSPump.DataPumpTransformLowerString")
    With oTransform
        .Name = "LowerCaseTransform"
        .SourceColumns.AddColumn "au_fname", 1
        .SourceColumns.AddColumn "au_lname", 2
        .DestinationColumns.AddColumn "FirstName", 1
        .DestinationColumns.AddColumn "LastName", 2
    End With
    
    'link transform to task, task to package, run package
    oCustTask.Transformations.Add oTransform
    oPackage.Tasks.Add oTask
    oPackage.Execute
End Sub