Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
SQLDTS File Transfer Protocol Task
By Darren Green
Version 7.0/2000
Level Intermediate

The SQLDTS FTP Task is designed to give you greater control over FTP operations compared to the task supplied with SQL Server 2000, as well as supporting SQL Server 7.0.

The task supports both upload and download operations, as well as exposing options such as port, connection type and transfer type.

Other features include wildcard support, file filters with date parameters and the ability to return a collection of transferred file information to a global variable for further use in the package. The File Filter feature allows you to use the standard asterisk wildcard character, as well as the custom date format parameters. You can use any standard combination of date format definition characters and separator characters, but the format must be enclosed in angle brackets, <format>. This will be replaced at runtime with the current date and time as defined by the format. The date offset function allows you to add or subtract time periods from the current time for use as the date replacement. For example if on 30 November 2002 you use a file filter of <yyyymmdd>.log this will be translated to 20021130.log at run-time. Similarly a format of FilePrefix_<dd_mm_yyyy>.txt would result in FilePrefix_30_11_2002. If you combine this with the date offset of -86400 this will translated to FilePrefix_26_11_2002.log.

The Write FTP Files collection of copied files to global variable for later use option is just as described. If using wildcards you will want to know what files have been processed, so this option will store a custom collection of the files processed in a global variable. The simple ActiveX Script sample below assumes you have checked the option and you have selected a global variable called ResultFiles. It enumerates through the collection and displays the filename for all files found in a message box.

Option Explicit

Function Main()

    Dim oFTPFiles    ' ProgID - SQLDTSFTPTask.Files
    Dim oFTPFile     ' ProgID - SQLDTSFTPTask.File
    
    Set oFTPFiles = DTSGlobalVariables("ResultFiles").Value

    For Each oFTPFile In oFTPFiles
        MsgBox oFTPFile.Name
    Next

    Set oFTPFile = Nothing
    Set oFTPFiles = Nothing
    
    Main = DTSTaskExecResult_Success
End Function

The File Transfer Protocol and Zip Task uses the Microsoft Win32 Internet functions (WinInet), so of course it has the same limitations. Of particular note in this release are the following:

  • You cannot download an individual file if that file's name contains a space. Using a wild card file filter or selecting a directory will allow you to workaround this.

Due to the threading limitations of Visual Basic 6, which was used to write this custom task, the task itself must be set to execute on the main package thread. This should be set automatically by DTS Designer. As well as the task, any secondary task that uses the main task, such as the Dynamic Properties Task or an ActiveX Script Task that changes task properties or uses the global variables collection must also be set to execute on the main package thread.

The task properties are not always directly exposed at the custom task level, such as when accessed through an ActiveX Script Task inside the package. In this case task properties can only be accessed through the Properties collection as shown below.

Option Explicit

Function Main()

    Dim oPkg
    Dim oFTPTask

    Set oPkg = DTSGlobalVariables.Parent
    Set oFTPTask = oPkg.Tasks("DTSTask_SQLDTSFTPTask.FTPCustomTask_1").CustomTask

    oFTPTask.Properties("FTPServer").Value = DTSGlobalVariables("FTPServer").Value
    oFTPTask.Properties("UserName").Value = DTSGlobalVariables("FTPUserName").Value
    oFTPTask.Properties("Password").Value = DTSGlobalVariables("FTPPassword").Value

    Set oFTPTask = Nothing
    Set oPkg = Nothing

    Main = DTSTaskExecResult_Success
End Function
Properties
Property Data Type Description
AccessMethod SQLDTSFTPTaskAccessMethod Value of SQLDTSFTPTaskAccessMethod indicating authentication access method.
ConnectionType SQLDTSFTPTaskConnectionType Value of SQLDTSFTPTaskConnectionType indicating FTP connection type.
DateOffset Long Number of seconds to use as date offset for filenames using date/time tokens.
DeleteExistingFiles Boolean Flag indicating if destination files should be deleted if it already exists.
DeletePostTransfer Boolean Flag indicating if source files should be deleted post transfer.
Description String The textual description of a task.
Direction SQLDTSFTPTaskDirection Value of SQLDTSFTPTaskDirection indicating transfer direction.
ErrorOnSourceNotFound Boolean Flag indicating if and error should be raised when the source file does not exist.
FTPServer String The FTP server to connect to.
LocalPath String The local file path. Not fully implemented, see TransferXML.
Name String The name of the task.
Password String FTP password. (Write Only)
Port Long FTP port.
RemotePath String The remote file path. Not fully implemented, see TransferXML.
ResultFilesGlobalVariable String The name of the global variable in which to store the result file collection.
TransferType SQLDTSFTPTransferType Value of SQLDTSFTPTransferType indicating the FTP transfer type.
TransferXML String XML document detailing files and folders to be transferred.
UserName String FTP user name.
Constants
SQLDTSFTPTaskAccessMethod
Constant Value Description
SQLDTSFTPTaskAccessMethod_Authenticated 0 Use supplied FTP credentials.
SQLDTSFTPTaskAccessMethod_Anonymous 1 Use anonymous FTP credentials.
SQLDTSFTPTaskDirection
Constant Value Description
SQLDTSFTPTaskAccessMethod_Download 0 FTP transfer direction is download.
SQLDTSFTPTaskAccessMethod_Upload 1 FTP transfer direction is upload.
N.B. Individual constant names do not match enumeration name. Fix postponed until next major release to preserve binary compatibility.
SQLDTSFTPTaskConnectionType
Constant Value Description
SQLDTSFTPTaskConnectionType_Active 0 Use active FTP connection.
SQLDTSFTPTaskConnectionType_Passive 1 Use passive FTP connection. Required for some firewalls.
SQLDTSFTPTaskType
Constant Value Description
SQLDTSFTPTaskType_File 0 Transfer task type if file.
SQLDTSFTPTaskType_Folder 1 Transfer task type is folder.
SQLDTSFTPTransferType
Constant Value Description
SQLDTSFTPTransferType_Unknown &H0 Unknown.
SQLDTSFTPTransferType_ASCII &H1 ASCII.
SQLDTSFTPTransferType_Binary &H2 Binary.
TransferXML Property

A sample of the transfer XML is shown below

<xml xmlns="sqldts">
	<transfer 
		source="<Source Path>" 
		destination="<Destination Path>" 
		type="<SQLDTSFTPTaskType Value>" 
		status="0" 
		error=""/>
</xml>

To find out more download it and give it a try.

Please mail any bug reports here.
Remove the tinned meat decoy from the email address before sending.

Version History

Version 1.1.12 - Fixed UI bug for UNC paths. Fixed bug with incorrect task result being reported in certain situations.
(6 July 2004)

Version 1.1.0 - Updated internal file path handling for reliability.
(7 June 2004)

Version 1.0.18 - Fixed bug with user default folders below the root.
(26 April 2004)

Version 1.0.16 - Fixed various UI bugs including FTP folder navigation issues. Removed redundant help functions.
(21 April 2004)

Version 1.0.14 - Public Release (Beta)
(9 January 2003)