DataPumpTransformDateTimeString Object

The DataPumpTransformDateTimeString object converts a datetime string in one format to another datetime format. It requires one source and one destination column, both of data types compatible with the OLE DB data type DBTIMESTAMP. The transformation properties InputFormat and OutputFormat specify the formats of the source and destination columns, respectively.

Properties

AMSymbol Property	Month??ShortName Property
Day?LongName Property	OutputFormat Property
Day?ShortName Property	PMSymbol Property
InputFormat Property	ShortYear2000Cutoff Property
Month??LongName Property

Methods

Remarks

The New method of the Transformations collection of the DataPumpTask2, DataDrivenQueryTask2, and TransformationSet objects returns a reference to a Transformation2 object. The TransformServer property of the Transformation2 object returns a reference to the DataPumpTransformDateTimeString object.

Example

This example Microsoft® Visual Basic® program transforms a date column in the employee table of the pubs database, where dates are in a short date format, to column HireDate in table Employee in a Microsoft Access database D:\DTS_UE\Data\jetPubs.mdb. For example, 5/1/94 is converted to May 01, 1994 (Sunday):

Public Sub Main()
'Copy/reformat pubs..employee.hire_date to Access DB.
    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 oDateTime   As DTSPump.DataPumpTransformDateTimeString
    
    Set oPackage = New DTS.Package
    oPackage.FailOnError = True
    
    'Establish connection to SQL Server DB.
    Set oConnect = oPackage.Connections.New("SQLOLEDB.1")
    With oConnect
        .ID = 1
        .DataSource = "(local)"
        .UseTrustedConnection = True
    End With
    oPackage.Connections.Add oConnect

    'Establish connection to Access database.
    Set oConnect = oPackage.Connections.New( _
                                "Microsoft.Jet.OLEDB.4.0")
    oConnect.ID = 2
    oConnect.DataSource = "D:\DTS_UE\Data\JetPubs.mdb"
    oPackage.Connections.Add oConnect
    
    'Create step and task, link step to task.
    Set oStep = oPackage.Steps.New
    oStep.Name = "DateTimeStep"
    Set oTask = oPackage.Tasks.New("DTSDataPumpTask")
    Set oCustTask = oTask.CustomTask
    oCustTask.Name = "DateTimeTask"
    oStep.TaskName = oCustTask.Name
    oPackage.Steps.Add oStep
    
    'Link task to connections.
    With oCustTask
        .SourceConnectionID = 1
        .SourceObjectName = "[pubs].[dbo].[employee]"
        .DestinationConnectionID = 2
        .DestinationObjectName = "Employee"
    End With
    
    'Create custom transform, link to source and dest columns.
    Set oTransform = oCustTask.Transformations. _
            New("DTSPump.DataPumpTransformDateTimeString")
    oTransform.Name = "DateTimeTransform"
    Set oColumn = oTransform.SourceColumns.New("hire_date", 1)
    oTransform.SourceColumns.Add oColumn
    Set oColumn = oTransform.DestinationColumns. _
            New("HireDate", 1)
    oTransform.DestinationColumns.Add oColumn
    
    'Define source and destination date formats.
    Set oDateTime = oTransform.TransformServer
    oDateTime.InputFormat = "M/d/yy"
    oDateTime.OutputFormat = "MMMM dd, yyyy (dddd)"

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

See Also