Introduction
In SQL Server Integration Services (SSIS), tasks like FTP and SFTP are commonly used to automate the process of transferring files between servers. While both tasks serve a similar purpose, they differ significantly, especially when it comes to security. The FTP Task uses the File Transfer Protocol (FTP), which transmits data in plaintext and is therefore not secure. In contrast, the SFTP Task utilizes the SSH File Transfer Protocol (SFTP), providing encrypted communication to ensure secure file transfers.
In this tutorial, we’ll compare the FTP Task and the SFTP Task in SSIS, explaining their features, advantages, and how to implement them in SSIS workflows using ZappySys components.
Prerequisites
- Download and install the ZappySys SSIS PowerPack.
Comparison
FTP Task
The FTP Task in SSIS allows you to transfer files over the File Transfer Protocol (FTP). While this is suitable for simple file transfers, FTP is unencrypted, making it vulnerable to eavesdropping. For environments requiring high security, FTP is not recommended.
The FTP Task supports the following operations:
- Send Files
- Receive Files
- Create Files
- Remove Files
SFTP Task
The SFTP Task in SSIS uses the SSH File Transfer Protocol (SFTP), which encrypts the entire communication, ensuring secure file transfers. If your organization handles sensitive information or operates in a regulated environment, SFTP is the preferred choice.
The SFTP Task supports all the actions available in the FTP Task, with the addition of:
- Get File Size
- Get File Status
- Get Creation/Modification Date
- Get a List of Files/Folders
- And more…
Steps
In the following examples, we will transfer a file between a local machine and an FTP/SFTP server using both tasks.
FTP Task
-
Drag the FTP Task into the Control Flow.
-
Double-click the FTP Task and provide a descriptive name.
-
Under the FTP Connection Manager, click New Connection to configure the FTP server connection.
-
Enter the server name, port, and login credentials.
-
Test the connection and click OK to save the configuration.
-
Click on the File Transfer tab.
-
Under the Operation section, choose the Send files option to upload a file to the FTP server.
-
In the Source File field, select the file to upload from the local machine.
-
In the Destination Folder field, specify the target directory on the FTP server.
-
Save the configuration and click OK.
-
Run the package, and the file will be uploaded to the FTP server.
SFTP Task
-
Drag and drop the SFTP Task into the Control Flow.
-
Open the task.
-
In the Action section, select Upload local file(s) to FTP server.
-
In the Source Path field, browse and select the file to upload.
-
Under the SFTP Connection Manager, click New Connection to configure the SFTP connection.
-
Enter the server’s host, port, and login credentials. You can review other settings in the tabs if further configuration is needed.
-
Test the connection and click OK to save the configuration.
-
In the Destination Folder field, specify the target folder on the SFTP server.
-
Optionally, check the Overwrite file if it exists option, depending on your requirements.
-
Click OK to save the configuration.
-
Run the package, and the file will be securely uploaded to the SFTP server.
Additional Functions
-
Use wildcards in the Path field to apply actions to multiple files (e.g.,
D:\Zappysys\new CSV files\*.csv). -
Under the Filter and Sorting tab, you can:
- Apply Regex patterns to include/exclude files.
- Enable Sorting by name, size, or date.
- Use Top to limit the number of files or Skip to bypass the first N files.
- Use the Where field to filter files by size, date, extension, etc. For more information, click Examples.
Conclusion
The FTP Task is a good choice for environments where file transfer security is not a concern, as it is easier to configure but lacks encryption. On the other hand, the SFTP Task is the better choice for secure file transfers, especially in environments where data protection is a priority. The ZappySys SFTP Task offers the same functionalities as the FTP Task but with the added benefit of encrypted communication, ensuring that sensitive data is protected during transfer. For any organization dealing with private or regulated information, SFTP should be the default choice.
Explore our SSIS PowerPack for more information and download and install it to start building smarter, faster, and more scalable solutions.
References
Contact us
If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.