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.
- Create a new package in LearnSSIS1 solution and rename it to "FTP.dtsx". Then open the package if it is closed.
- Right click anywhere in Connection Manager and choose "New Connection..." in the pop-up window.
- In the pop-up "Add SSIS Connection Manager" window, choose "FTP" and click "Add..." button.
- The window "FTP Connection Manager Editor" will be opened. Input "localhost" in the Server name box and leave all the others as defaults.
- 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.
| Field | Default Value | Description |
|---|---|---|
| Server name | The FTP server name or IP address. | |
| Server port | 21 | The port of the FTP server. |
| User name | anonymous | The 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) | 60 | The number of seconds before timing out. |
| Use passive mode | (unchecked) | The client initiates the connection if checked. |
| Retries | 5 | The number of times to make a connection. 0 means unlimited. |
| Chunk size (in KB) | 1 | The 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.
- 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.
- Drag and drop FTP Task to the Control Flow of the FTP.dtsx package.
- Right click the task and choose "Edit..." to open Ftp Task Editor. Then choose the "FTP Connection Manager" as FtpConnection.
- 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.
- 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.
| Operation | Description |
|---|---|
| Send files | Send a file from local machine to the FTP server. |
| Receive files | Get a file from the FTP server to the local machine. |
| Create local directory | Create a folder on the local machine. |
| Create remote directory | Create a folder on the FTP server. |
| Remove local directory | Delete a folder on the local machine. |
| Remove remote directory | Delete a folder on the FTP server. |
| Delete local files | Delete a file on the local machine. |
| Delete remote files | Delete a file on the FTP server. |
The other fields except operations are summarized below.
| Field | Default Value | Description |
|---|---|---|
| IsLocalPathVariable | False | The LocalPath is saved in a variable if True |
| LocalPath | A variable if IsLocalPathVariable is set to True otherwise a file connection. | |
| IsTransferAscii | False | Transfer files in ASCII mode or not. |
| IsRemotePathVariable | False | The 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. | |
| OverwriteFileAtDest | False | The destination file is overwritten if True. |
 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.