SSIS Ftp Task

FTP means File Transfer Protocol which is used to transfer files from one server to another one over TCP network, for example, Internet. The FTP Task is to realize the FTP functions in SSIS package. Like SMTP connection manager, a FTP connection manager is needed to be configured separately and used in FTP task.

Let's create it step by step.

  1. Create a new package in LearnSSIS1 solution and rename it to "FTP.dtsx". Then open the package if it is closed.

  2. Right click anywhere in Connection Manager and choose "New Connection..." in the pop-up window.
  3. SSIS New Connection Manager

  4. In the pop-up "Add SSIS Connection Manager" window, choose "FTP" and click "Add..." button.
  5. Add SSIS Connection Manager

  6. The window "FTP Connection Manager Editor" will be opened. Input "localhost" in the Server name box and leave all the others as defaults.
  7. FTP Connection Manager Editor

  8. Click "Test Connection" button. If succeeded, click "OK" to close the editor.

A "FTP Connection Manager" was created in Connection Managers area. You can reopen the editor window by right click the manager and choose "Edit...".

The following is a table of the settings in FTP Connection Manager Editor.

FieldDefault ValueDescription
Server name The FTP server name or IP address.
Server port21The port of the FTP server.
User nameanonymousThe user ID of the account to access the FTP server.
Password The password of the account to access the FTP server.
Time-out (in seconds)60The number of seconds before timing out.
Use passive mode(unchecked)The client initiates the connection if checked.
Retries5The number of times to make a connection. 0 means unlimited.
Chunk size (in KB)1The size of each packet involved in the transfer.

In the above example, localhost was put as a FTP server because a FTP server was installed on my local machine and the anonymous account is set to be allowed. If the anonymous account is not allowed, a user name and password must be required.

To use the FTP connection manager we just created, we'll add a FTP task to the package to send a file to the FTP server.

  1. Create a simple TXT file D:\zz.txt and create a variable "SourceFile" with string type and its value is set to D:\zz.txt.

  2. Drag and drop FTP Task to the Control Flow of the FTP.dtsx package.

  3. Right click the task and choose "Edit..." to open Ftp Task Editor. Then choose the "FTP Connection Manager" as FtpConnection.
  4. SSIS FTP Task Editor

  5. Click "File Transfer" tab and set IsLocalPathVariable to True and LocalVariable to User::SourceFile. Set Operation to "Send files", RemotePath to "/" and leave other options as defaults.
  6. SSIS FTP Task Editor File Transfer Settings

  7. Click "OK" button and run the package.

After the package is run, the file zz.txt is transferred to the root of the FTP site.

Here is a list of all the operations in the "File Transfer" tab.

Send filesSend a file from local machine to the FTP server.
Receive filesGet a file from the FTP server to the local machine.
Create local directoryCreate a folder on the local machine.
Create remote directoryCreate a folder on the FTP server.
Remove local directoryDelete a folder on the local machine.
Remove remote directoryDelete a folder on the FTP server.
Delete local filesDelete a file on the local machine.
Delete remote filesDelete a file on the FTP server.

The other fields except operations are summarized below.

FieldDefault ValueDescription
IsLocalPathVariableFalseThe LocalPath is saved in a variable if True
LocalPath A variable if IsLocalPathVariable is set to True otherwise a file connection.
IsTransferAsciiFalseTransfer files in ASCII mode or not.
IsRemotePathVariableFalseThe RemotePath is saved in a variable if True
RemotePath Set remote path if IsRemotePathVariable is set to False otherwise this field is hidden.
RemoteVariable A variable if IsRemotePathVariable is set to True otherwise this field is hidden.
OverwriteFileAtDestFalseThe destination file is overwritten if True.

Note Note
RemotePath must be set to start with "/" and the value of the RemoteVariable must start with "/" as well.

If the properties need changing dynamically, the setting in the Expressions tab in FTP Task Editor is needed.