Introduction
This article will demonstrate how to use an SFTP server with the ZappySys SSIS PowerPack, utilizing the Rebex Tiny SFTP Server tool. This guide will walk through setting up the SFTP server and performing various operations, such as downloading files, filtering based on file attributes, renaming files, and creating folders using the SSIS SFTP Task.
Prerequisites
- SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from here.
- Rebex Tiny SFTP Server: Ensure you have Rebex Tiny SFTP Server installed, which you can download here.
Steps
Step 1: Configure Rebex Tiny SFTP Server
- Download Rebex Tiny SFTP Server here.
- Extract the tool and open the file RebexTinySftpServer.exe.config using a text editor like Visual Studio or Visual Studio Code.
- Modify the necessary configuration values such as userName, userRootDir, and port according to your preference, then save the file.
- Run the RebexTinySftpServer.exe application and click Start to initiate the SFTP server.
- Place test files (e.g., text, Excel, Word) inside the data folder for testing with the ZappySys tool.
Step 2: Create an SFTP Connection in SSIS
- In SSIS, drag and drop the SFTP Task into the Control Flow.
- Inside the SFTP Task, create a new connection and fill in the necessary details for the SFTP server. Use ’ localhost ’ for the Hostto connect to the locally hosted Rebex Tiny SFTP Server.
Step 3: User Cases and Examples
Example 1: Download Files with Different Extensions
You can use wildcards in the path to download multiple types of files.
Alternatively, use regular expressions (RegEx) to include or exclude specific file types. You can find more RegEx examples here and test expressions on this page.
Example 2: Use Variables for the File Name
You can insert variables into the file path using either the folder name or the full path.
Example 3: Filter Files by Size
Use the Where clause to filter files by attributes like file size in bytes.
Example 4: Filter Files by Creation Date
To filter files based on their creation date, use the following example and enter the desired date.
Example 5: Rename Files
You can rename files using variables or use predefined options in the path, such as:
{%ext%}: File extension{%timestamp%}: Timestamp for unique identification{%name%}: Original file name
Example 6: Create Folders
To create a new folder, select the target folder and type the name for the new folder.
Conclusion
With the ZappySys SSIS PowerPack and an SFTP server like Rebex Tiny SFTP Server, you can perform various SFTP operations, such as downloading, filtering, renaming, and creating files or folders. For more detailed assistance, contact the ZappySys support team.