In this article, we will explain the File Transfer Protocol (FTP), and we will give an overview of the FTP task in SQL Server Integration Services (SSIS) and FTP connection manager.
Introduction
As the name implies, File Transfer Protocol (FTP) is a network protocol to transfer files between computers. This protocol is built on a client-server model. Using FTP you can send or receive files; sending files is noted as “put” and receiving is noted as “get”.
You don’t need to go through more details about FTP, the main thing you should know is that even you didn’t hear about this protocol you have surely used it when uploading or downloading files and applications from your web browser or download manager.
If you want to learn more about FTP, you can do a simple search for “FTP” word, and there are plenty of articles online that describes this protocol very well.
In the following sections, we will describe how to establish an FTP connection and perform FTP Tasks using SQL Server Integration Services. To run an example, we will enable the FTP server feature on our local machine (Windows 10).
SSIS FTP connection manager
Before illustrating the SSIS FTP Task, we should explain the FTP connection manager, since it is used to establish a connection with an FTP server which is required by the FTP Task.
If you right-click on in the connection manager tab, you may note that this type of connection manager is not available in the context menu strip. To add this connection manager, you must click on the “New connection” button as shown below:
Figure 1 – Adding an FTP connection manager
Then from the connection managers list, you must choose the FTP connection manager:
Figure 2 – Selecting FTP from connection managers list
After selecting the FTP connection manager, the connection manager form appears. To run an example, we have created an FTP site on port 21 which read from a local folder and doesn’t require permissions. Based on that we configured the connection manager as shown below:
Figure 3 – FTP connection manager configuration
You can note that the FTP connection manager contains the following properties:
- Server Name: The FTP server name (example: ftp.companyame.com)
- Server Port: The port number used
- User Name: The user name used for authentication
- Password: The password used for authentication
- Use passive mode: If checked the client activates the connection (passive mode), else the server initiates the connection
- Retries: Number of retires allowed to establish a connection
- Test Connection: check whether our connection is successful or not
You can read more about the FTP connection manager on the Microsoft official documentation.
SSIS FTP Task
After creating an FTP connection manager, we should add an SSIS FTP Task to download and upload files and manages directories on the FTP server. As shown in the image below, the FTP Task is described as a task that “Works with FTP Servers. Manage directories or download and upload data files”.
Figure 4 – FTP Task description from SSIS Toolbox
In the SSIS FTP Task editor form, there are three tab pages:
General: On this page, we should specify the task name, description, the FTP connection manager we will use to establish a connection and whether this task stops on FTP operation failure
Figure 5 – FTP Task general tab page
File Transfer: On this page, we can configure the operation we need to execute. As shown in the image below, there is a different operation that can be executed such as:
- Send files: Upload files from your machine to the FTP server
- Receive files: Download files from the FTP server
- Create a local directory: Create a directory on your machine
- Create a remote directory: Create a directory on the FTP server
- Remove local directory: Delete a directory on your machine
- Remove remote directory: Delete a directory on the FTP server
- Delete local files: Delete files from your machine
- Delete remote files: Delete files from the FTP server
And each one of these operations has its configuration, you will note that the available properties on this page will change when the operation type change
Figure 6 – FTP task file transfer tab page
- Expressions: This page is used to evaluate the SSIS FTP Task properties as expressions (similar to any other SSIS task)
In this example, we will upload files from the local machine to the FTP server, so we will select the “Send Files” operation type. As shown in the image below, we have to set the following parameters:
- Local Parameters: which are related to the files we need to upload:
- IsLocalPathVariable: Specify if we need to read the local path from an SSIS variable
- LocalPath: Specify the local file path; if IsLocalPathVariable is set to true then we have to select the SSIS variable. Else, we need to select a File Connection Manager
- Operation: which are related to the FTP operation:
- Operation: The operation type (we mentioned it above)
- IsTransferASCII: Indicate whether files transferred to and from the remote FTP server should be transferred in ASCII mode
- Remote Parameters: which are related to the destination (FTP Server)
- IsRemotePathVariable: Specify if we need to read the destination path from an SSIS variable
- RemotePath: Specify the destination file path; if IsRemotePathVariable is set to true then we have to select the SSIS variable. Else, we need to enter it manually
- OverwriteFileAtDest: Select if we want to overwrite the existing file on the FTP server
Figure 7 – Configuring FTP Task
After configuring the FTP task, we execute the package. If we go to the remote directory, we will see that the file is uploaded successfully.
Figure 8 – File uploaded to the remote path
If you need to read more information about different SSIS FTP Task operations, you can refer to the Microsoft official documentation.
Conclusion
In this article, we have briefly described File Transfer Protocol (FTP) and how to establish FTP connections and how to perform FTP operations such as uploading and downloading files in SQL Server Integration Services.
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023