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
Methods
| GetDayLongName Method | SetDayLongName Method |
| GetDayShortName Method | SetDayShortName Method |
| GetMonthLongName Method | SetMonthLongName Method |
| GetMonthShortName Method |
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