DTS Programming

Remarks

If the file name column contains a path, it can use either a drive letter or a universal naming convention (UNC) file specification. If no path is present, the FilePath property can be used to supply the path. However, FilePath is always used when it is nonempty, even when the file name column contains a path.

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

Example

This example Microsoft® Visual Basic® program transforms a column in a Microsoft Excel sheet to a database column using the Read File custom transformation. Column file_name (row 1 of the column contains the label "file_name") of worksheet FileSpecTwo in D:\DTS_UE\Source\FileSpecs.xls contains the file names. The transformation writes the file data to column file_data in table FileDataOut in database DTS_UE on the local server:

Public Sub Main()
'Read file names from Excel worksheet. Write file data to database column.
    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 oReadFile   As DTSPump.DataPumpTransformReadFile

    Set oPackage = New DTS.Package
    oPackage.FailOnError = True
    
    'Establish connection to source Excel worksheet.
    Set oConnect = oPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")
    With oConnect
        .ID = 1
        .DataSource = "D:\DTS_UE\Source\FileSpecs.xls"
        .ConnectionProperties("Extended Properties") = _
                                "Excel 8.0;HDR=YES;"
    End With
    oPackage.Connections.Add oConnect

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

    'Create the step and task, and link the step to the task
    Set oStep = oPackage.Steps.New
    oStep.Name = "ReadFileStep"
    Set oTask = oPackage.Tasks.New("DTSDataPumpTask")
    Set oCustTask = oTask.CustomTask
    oCustTask.Name = "ReadFileTask"
    oStep.TaskName = oCustTask.Name
    oPackage.Steps.Add oStep
    
    'Link the task to the connections, and specify worksheet, database and table.
    With oCustTask
        .SourceConnectionID = 1
        .SourceObjectName = "FileSpecTwo$"
        .DestinationConnectionID = 2
        .DestinationObjectName = "DTS_UE..FileDataOut"
    End With
    
    'Create transform, and link it to the source and destination columns.
    Set oTransform = oCustTask.Transformations. _
            New("DTSPump.DataPumpTransformReadFile")
    oTransform.Name = "ReadFileTransform"
    Set oColumn = oTransform.SourceColumns.New("file_name", 1)
    oTransform.SourceColumns.Add oColumn
    Set oColumn = oTransform.DestinationColumns. _
            New("file_data", 1)
    oTransform.DestinationColumns.Add oColumn
            
    'Define error action and path prefix.
    Set oReadFile = oTransform.TransformServer
    oReadFile.ErrorIfFileNotFound = False
    oReadFile.FilePath = "D:\DTS_UE"

    '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

New (ID) Method

Transformation2 Object

Transformations Collection

TransformationSet Object

TransformServer Property