The SFTP connection manager is used to create a connection manager with an SFTP site. It is used with the Secure FTPtask.
|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.|
|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.|
|Use Proxy||Selecting this allows the use of a proxy, to use the proxy you must fill in the information below the check box.|
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.
|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.|
|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 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.|
|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.|
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.
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.
- 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
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.
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.
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.
[ExecuteTask Failed:] Information: Could not authenticate user [Username]
[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 188.8.131.52:49088
[ExecuteTask Failed:] Error: File /(SFTPFilePath) doesn't exist on the server.
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.