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)