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

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 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)