SSIS SFTP Connection - How to connect to SFTP Tutorial

SSIS PowerPack FTP / SFTP
1

Introduction

This article guides you through using our SSIS SFTP Task (Secure FTP, FTPS) and provides tips and possible solutions.

The SSIS SFTP Task can perform many operations using SFTP, FTP/SSL (i.e., FTPS), or Classic FTP Protocol. It supports advanced options and authentication mechanisms like NTLM, Kerberos, Client Certificate, and Private Key.

Prerequisites

  • SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from this site.

Steps

Create a Secure FTP connection

  1. Right-click on Connection Managers, select New Connection…, then ZS-SFTP.

  2. In the Connection Manager UI, select SFTP - SSH File Transfer Protocol, enter your Hostname, and select Logon. Enter your Username and Password.

    SFTP connection option 1
    SFTP connection option 1593×593 32.2 KB

  3. For the FTP - File Transfer Protocol option, enter your Hostname and select Encryption type. Enter your Username and Password. SFTP Connection option 2

  4. If you have a Proxy configuration, select the proxy tab and fill in the Proxy type, server, and credentials fields. SFTP Connection proxy tab

  5. Configure Encryption Algorithms, Server Key size, disable options, and more under the Settings tab. Suppose you encounter the error message Disconnected by the server. Key Exchange failed, refer to this article for help.

    SFTP settings tab
    SFTP settings tab658×569 24.3 KB

  6. Configure SSL/TLS versions and more under the SSL/TLS tab. SFTP connection SSL/TLS tab

  7. Utilize more options like Transfer mode, Server fingerprint or certificate, max upload/download speed, and others under the Advanced settings tab.

    SFTP connection Advanced settings tab
    SFTP connection Advanced settings tab584×594 37.4 KB

  8. Click Test connection to verify your configuration and then click OK to save it.

Use our SFTP task

  1. Drag and drop our SFTP task to the Control Flow, then double-click it.
  2. Select the action you want; in this case, we selected Download FTP server File(s) to local directory.
  3. Choose the connection created in the previous step.
  4. Select a path from the server; you can select one file or use a wildcard to get more than one file.
  5. Select the local file path; use a variable as a path, select a variable, and choose the overwrite option if you want to overwrite or throw an error if the file exists.
    SFTP task example
    SFTP task example665×599 41.3 KB
  6. Click on OK to save the configuration.
  7. Run the package or only the task to check the result.

SFTP task options

Explore a list of actions you can use with our tool; each action has a similar configuration as the example we used before:

  1. Download File From FTP: Download FTP server file(s) to a local directory.
  2. Upload File To FTP: Upload local file(s) to FTP server.
  3. Delete FTP File: Delete FTP file(s).
  4. Delete FTP Folder: Delete FTP folder(s).
  5. Move FTP File To FTP: Move FTP file(s).
  6. Rename FTP File: Rename FTP file(s).
  7. Create New FTP File: Create a new FTP file.
  8. Create FTP Folder: Create a new FTP folder.
  9. Create FTP Folder If Not Exists: Create a new FTP folder if it does not exist.
  10. Get FTP File Size: Get FTP file(s) size.
  11. Get FTP File Exists Flag: Get FTP file exist status.
  12. Get FTP File Count: Get FTP files count.
  13. Get FTP File Last Modified Date: Get FTP file LastModifiedDate.
  14. Get FTP File Creation Date: Get FTP file CreationDate.
  15. Get FTP File Last Access Date: Get FTP file LastAccessDate (Last Read).
  16. Get FTP File Path Latest: Get the latest file path (Sort By LastModifiedDate).
  17. Get FTP File Path Oldest: Get the oldest file path (Sort By LastModifiedDate).
  18. Get FTP File Data As String: Read FTP File content as text.
  19. Get FTP File Data As Binary: Read FTP File content as a byte array.
  20. Get FTP Folder Exists Flag: Get FTP folder exist status.
  21. Get FTP File List As ADO NET DataTable: Get FTP file list as ADO.net DataTable.
  22. Get FTP Folder List As ADO NET DataTable: Get FTP folder list as ADO.net DataTable.

FAQ

Check our FAQ article for our SFTP task here. It will help you resolve any issues you might encounter.

Conclusion

Following these steps, you can easily configure our SFTP component in SSIS. For further assistance or inquiries, contact our support team via chat on our website.

Reference

For more detailed information, refer to our SSIS SFTP Task (Secure FTP, FTPS) article.