Task Factory users running version 2020.1.4 or older (released prior to May 27, 2020): There's an important Task Factory update. Please visit here for more details.
The SFTP connection manager is used to create a connection manager with an SFTP site. It is used with the Secure FTP task.
|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 backslashes to define the directory.|
|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.|
Configuring the SFTP Connection Manager
To configure the Task Factory SFTP connection manager in your Visual Studio Package, complete the following steps:
Note: This example uses the free demo SFTP servers that can be found on Free Public SFTP Servers.
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.
Note: A Connected Successfully message prompt displays if your connection was successful. Select OK to close the prompt.
6. Select OK to save your TF SFTP connection manager.
Note: For more information about Advanced Settings, or using a Proxy for your connection, see the Advanced Settings, and Proxy sections of this article.
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.|
|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
|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
Important: When configuring the component to rename a file, the New File Name must include the path. For example, renaming a file in the documents folder is configured /Documents/RenamedFile.ext.
|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.|
Additional Information: See the Expressions article for details on using the Expressions button.
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
Note: You need to configure an SFTP connection manager for your Secure FTP Task. For more information about configuring an SFTP Connection Manger, go to the Connection Manager tab.
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.
Note: For information about configuring an SFTP Connection Manager, see the Connection Manager section.
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.
Note: For a detailed list of actions this task can take, see Task Settings table in above.
Important: Your selection will change the configurable task options.
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.
Note: For more specific Task Factory Secure FTP Task tutorials, see the Tutorials section.
- 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
STFP Task Examples
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.
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
Warning: You must use the same version of Task Factory across all your environments (dev, production, qa, etc.) to ensure optimal performance of Task Factory components.
Important: If your organization has changed any of the ciphers, or algorithms within your packages, you may need to upgrade your version of Task Factory. If you aren't fully committed to an upgrade, or want to test an upgrade first, see Testing the upgrade.
Additional Information: If you are encountering any issues with the Secure FTP Connection Manager, or Secure FTP Task, you can begin Creating a log file to help identify the problems you're having.
[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 126.96.36.199: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.