DTS Programming

Remarks

The data column must be a string or binary data type. If it is NULL, no file is written. If AppendIfFileExists is set to FALSE and the file exists, it is deleted. If the file is empty, a zero-length file is created. The file name column cannot be NULL or empty. 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.

This transformation object does not write destination columns, but a destination connection must still be provided. If no other transformations write columns, no rows are written.

You must explicitly add the source columns to the SourceColumns collection, even if the source connection has only two columns. If you do not add the columns, the transformation assumes you are including all the source and destination columns. This causes an error because the transformation cannot reference destination columns.

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 database column to written flat files with a Write File custom transformation. In table WriteFileData in database DTS_UE, column file_spec provides the file names, and file_data provides the data.

Public Sub Main()
'Write the data in DTS_UE. WriteFileData to specified files.
    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 oWriteFile  As DTSPump.DataPumpTransformWriteFile

    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

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

    'Create a step and task, and then link the step to the task.
    Set oStep = oPackage.Steps.New
    oStep.Name = "WriteFileStep"
    Set oTask = oPackage.Tasks.New("DTSDataPumpTask")
    Set oCustTask = oTask.CustomTask
    oCustTask.Name = "WriteFileTask"
    oStep.TaskName = oCustTask.Name
    oPackage.Steps.Add oStep
    
    'Link the task to the connections, and specify tables.
    With oCustTask
        .SourceConnectionID = 1
        .SourceObjectName = "DTS_UE.dbo.WriteFileData"
        .DestinationConnectionID = 2
        .DestinationObjectName = "DTS_UE.dbo.WriteFileData"
    End With
    
    'Create transform, and link it to source columns.
    Set oTransform = oCustTask.Transformations. _
            New("DTSPump.DataPumpTransformWriteFile")
    With oTransform
        .Name = "WriteFileTransform"
        .SourceColumns.AddColumn "file_spec", 1
        .SourceColumns.AddColumn "file_data", 2
    End With
            
    'Define error action and path prefix.
    Set oWriteFile = oTransform.TransformServer
    With oWriteFile
        .ErrorIfFileExists = False
        .FileColumnName = "file_spec"
        .AppendIfFileExists = True
        .FilePath = "D:\DTS_UE"
    End With

    'Link transform to the task, the task to the package, and 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