Task Factory Secure FTP
Secure FTP Connection Manager
The SFTP connection manager is used to create a connection manager with an SFTP site. It is used with the Secure FTPtask.
General Tab
| Option | Description |
|---|---|
| Connection Type | There are five connection types that are supported:
Important: The SFTP - SSH FTP, FTPS - FTP over implicit TLS or SSL, FTPES - FTP over explicit TLS or SSL, and FTP connection types use the Chilkat connection service. Additional Information: For more information about the Chilkat connection service, see Chilkat.
Important: The SSH FTP Legacy connection type uses the edtFTPnet/PRO connection services. Use this connection type if you are using a version of Task Factory from 2017 or older. Additional Information: For more information about the edtFTPnet/PRO connection service, see edtFTPnet/PRO. |
| Host | The IP Address or URL of the FTP server. |
| Port | The port used for the FTP server. |
| User name | The user name used to login to the FTP server. |
| Authenticate | Select how you authenticate the connection.
|
| Password | This option is available if By Password is selected in the Authenticate section. |
| Key File | Available if By Key File is selected in the Authenticate section. You can use the file browser to locate the file. |
| Passphrase | Available if By Key File is selected in the Authenticate section. |
| Timeout (secs) | The number of seconds before the application stops trying to make a connection if it cannot connect. |
| Test Connection button | Use this button to test whether a connection can be made using the settings in the General and Advanced tab. |
Advanced Tab
| Options | Description |
|---|---|
| Default Remote Directory | The default remote directory tells the connection to change directories on the FTP server for each connection made to the server. You must use a forward slash ( / ) to define the directory. For example: /user/documents/file.txt |
| Transfer Buffer Size (bytes) | Specify the buffer size. |
| SSL Version | Select the SSL version:
|
| Use binary transfer | Selecting this option switches the mode of transfer from ASCII to Binary. Select this option if you are primarily working with non-text file. Note: Use binary transfer is only applicable to legacy connections. |
| Use passive mode | Deselecting this option switches to active mode. |
| Ignore server IP | Use when the server is sending back incorrect IP addresses. |
| Reuse Connection Across All Tasks | Select this to make the connection reusable for all tasks. |
| Enable Compression | Select this option to enable compression for the SFTP- SSH FTP, or SFTP - SSH FTP Legacy options. |
Proxy Tab
| Option | Description |
|---|---|
| Use Proxy | Selecting this allows the use of a proxy, to use the proxy you must fill in the information below the check box. |
Configuring the SFTP Connection Manager
To configure the Task Factory SFTP connection manager in your Visual Studio Package, complete the following steps:
1. Create a new, or open an existing, Visual Studio Integration Services project.
|
|
|
2. Right Click the Connection Managers pane, and then select New Connection to open the Add SSIS Connection Manager window.
3. Select the Task Factory SFTP Connection, then select Add to open the TF-SFTP Connection Settings configuration window.
4. Select your Connection Type from the drop down list. Enter your connection's Host address and port number in the appropriate fields. 
5. Enter the User name and Password associated with your connection, then select Test Connection to ensure that you can connect successfully to your SFTP server.
6. Select OK to save your TF SFTP connection manager.
Secure FTP Task
| Task Icon | Task Description |
|---|---|
|
The Secure FTP Task interacts with a secure FTP server. See the Secure FTP Connection Manager tab to learn more about setting up the connection manager. |
Task Settings
| Option | Description | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| What will this task do? | Choose what the task does:
|
||||||||||||||||||||||||
| Stop package on failure? | This fails the package if the selected action fails to complete successfully. Note: This option is selected by default. |
Local Directory or File Settings
| Option | Description |
|---|---|
| Local path store in a variable? | Choose if the path is in a variable, selecting this changes the Local Connection Manager drop down to Select Variable. |
| Local connection manager | Available if Local path stored in a variable is not selected. Choose or create a connection manager. |
| File Filter | Use for Upload directory to server task. By using the * symbol you can specify different filters. For example to only upload XML files you would use *.xml . |
| Include Subdirectories | Choose whether or not to include sub-directories for this task. |
| Overwrite local file(s)? | This allows the task to overwrite the files if they already exist. |
Remote Connection and Directory or File Settings
| Option | Description |
|---|---|
| SFTP Connection Manager | Select an existing or create a new SFTP connection manager. |
| Remote path stored in a variable? | Selecting this option changes the Directory on server to upload file: drop down context to <select variable>. |
| Directory on server to upload file | Depending on what action is selected, this option is used to define the directory or file to be used on the remote server to complete the selected action. |
| File Filter | This option is used to define a filter when using the Download directory from server option. |
| New name of file on server | Enabled when Upload file to server option is selected. Users can create or rename the destination file. Best practice is to include the file's extension. Example: Source file UploadThisFile.txt can be renamed to the destination server as SameFileWithDifferentName.txt |
| Select Result Variable | To use this action you must select a variable to contain the result of the file check. Once the SFTP Task completes, the selected variable contains the result. |
| Include Subdirectories | Choose whether or not to include subdirectories for this task. |
| Overwrite remote file(s)? | This allows the task to overwrite the files if they already exist. |
Expressions
SFTP Using a Connection String
This example shows you how to put the SFTP connection string in a variable, so that in the properties of the connection manager, you can set the expression of the connection string to take the created variable.
Configuring a Secure FTP Task
To configure a Secure FTP Task in your BIDS package, complete the following steps:
1. Create a new, or open an existing, Visual Studio Integration Services project.
2. Configure your TF SFTP Connection Manager.
3. Drag the TF Secure FTP Task component into your package Control Flow. Select the task, then select Edit to begin configuring the task properties.
4. Select what you want the task to do from the What will this task do drop-down list.
5. Select the connection manager for the task from the applicable drop-down list, and then enter your specifications for the remaining applicable settings.
6. Select OK to finish configuring your Task Factory Secure FTP Task.
Samples
See the taskfactory-samples repository on GitHub for Secure FTP Task samples:
- 1_SetupLocalSFTPDirectory.dtsx - Use to set up the SFTP Samples
- SecureFTP_CheckIfFileExists.dtsx - SFTP Task configured to check if a file exists on the server
- SecureFTP_DownloadDirectory.dtsx - SFTP Task configured to download a directory from the server
- SecureFTP_DownloadFileFromServer.dtsx - SFTP Task configured to download a file from the server
- SecureFTP_RenameFileOnServer.dtsx - SFTP Task configured to rename a file on the server
- SecureFTP_UploadDirectory.dtsx - SFTP Task configured to upload a local directory to the server
- SecureFTP_UploadFileToServer.dtsx - SFTP Task configured to upload a local file to the server
Tutorials
STFP Task Examples
Download list of files from SFTP Server
Tutorial walks through how to download a list of files from an SFTP server using the Task Factory Secure FTP Task. The initial setup of this process is covered in the KBA article titled Task Factory – SFTP – Get List of Files with Metadata. It is recommended that you review that article before continuing with this example.
Enumerating a ForEachLoop Container for Multiple SFTP Logins
Tutorial for if you want to store SFTP Login credentials outside of the SSIS package or want to use multiple credentials to download data from multiple Salesforce accounts.
Get a List of Files with Metadata
Tutorial shows how to use the Get List of Files with Metadata action in the TF Secure FTP Task. The tutorial covers how to write the metadata to a SQL table.
Video Tutorials
Configure an SFTP Task In Under One Minute
Read Metadata From an SFTP Task Using a Script Task
SFTP Upload Using A File Filter
SFTP Get Files With Metadata
SFTP Download Files Using File Filter
Troubleshooting
Troubleshooting Checklist
Troubleshooting Examples
SFTP Task Could Not Authenticate
[ExecuteTask Failed:] Information: Could not authenticate user [Username]
SFTP Task Connection Attempt Failed
[ExecuteTask Failed:] Error: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 129.130.13.3:49088
SFTP Task File Doesn’t Exist on the Server
[ExecuteTask Failed:] Error: File /(SFTPFilePath) doesn't exist on the server.
SFTP Task No Error
Issue in which the TF SFTP Task is not performing its designated function (i.e. Download File from Server, Upload File to server, etc.), but it’s showing as a success despite there being no sign the task actually performed the function requested.