Create FTP Task in SQL Server Integration Services

FTP tasks in SSIS (SQL Server Integration Services) are an important feature that is used to perform file operations across the server and client/local machine.

FTP task in SSIS is used to automate the process of uploading and downloading files to/from the FTP server. This task ensures the secure transfer of files, automates data workflow, and improves its efficiency. FTP Task, when combined with other SSIS tasks, can integrate data from multiple sources, such as File System Task, Script Task, Execute SQL Task, etc.

Now, let’s see how to create an FTP Task.

FTP is basically a File Transfer Protocol; it’s basically transferring files from one server to another server or to a local machine.

In the SSIS package within the control flow, we can see the SSIS Toolbox, and there are numerous options. From there we can select FTP Task by drag and drop it on package.

Below is the screenshot, which shows what it looks like.

In the above screenshot, we can see a red sign popped up; it means the connection is empty.

Note: I have used a personal FTP server and hence you may not be able to access it, you can however check a few public FTP servers available online.

Now, let’s configure the FTP Task and establish a connection. We will go on the FTP task editor and do the following.

1. Add FTP Server to establish FTP connection: Enter the below credential to establish an FTP connection. (FTP server path: ftp://Annu:[email protected])
2. Server Name: 192.168.1.6
3. User ID: Annu
4. PWD: ****

The connection has been established; we can see the screenshot below.

Now, enter the following details in file transfer and hit ok; the red sign will go away.

1. Add Operation as Send files
2. Add local path: C:\Users\Annu\Documents\FTP_Local
3. Add RemotePath: /

After hitting ok, you will see on the screen below that the red sign is gone. This means that both connections are established, and we can see this in Connection Manager.

The screenshot below shows that it was taken before executing the package, and you can see no files in the FTP Server are available currently.

Once step 2 is done, Execute the FTP Task by hitting the start button.

The screenshot below shows packages executed successfully. Here, our objective is to see if the file moved from the source (local path) to the destination (FTP Server).

Now, I will check into the FTP server to see if the file moved to the destination or FTP Server.

As you can see in the screenshot below, the file has indeed been moved to the FTP server.

Summary

There are several benefits of creating FTP Tasks in SSIS, for example.

• Automates the data transfer and reduces the time-consuming manual efforts.
• It helps integrate data from various sources by creating a workflow that automatically handles file transfers.
• Enables creation of FTP tasks for regular data backups and enhances data transfer security.