Send file to FTP Server in SSIS

This article demonstrate how to send, or upload file from local system to FTP Server.

SSIS allows you to use FTP task which helps you in downloading and uploading data files and manages directories on FTP servers. You can use the FTP task for the following operations:

Copying directories and data files from one directory to another, before or after moving data, and applying transformations to the data.
Logging in to a source FTP location and copying files or packages to a destination directory.
Downloading files from an FTP location and applying transformations to column data before loading the data into a database.

Following are the list of predefined operation that you can perform in FTP task.

Operation	Description
Send files	Sends a file from the local computer to the FTP server.
Receive files	Saves a file from the FTP server to the local computer.
Create local directory	Creates a folder on the local computer.
Create remote directory	Creates a folder on the FTP server.
Remove local directory	Deletes a folder on the local computer.
Remove remote directory	Deletes a folder on the FTP server.
Delete local files	Deletes a file on the local computer.
Delete remote files	Deletes a file on the FTP server.

Lets create a FTP task which send a file from local system to FTP Server.

Here we have one file in our local system named SplitCustomerData.xls, this file will be uploaded to FTP Server using SSIS File Task.

Lets take a look at FTP Server, as you can see, FTP Server has one directory named DataFiles and within that directory there is one file that is Exl_CustomerData.xlsx.

Lets open the SSDT (SQL Server Data Tools), and create new SSIS project or can create new package in existing SSIS project as Shown below.

Right click on SSIS Packages folder then select New SSIS Package

Lets give it an appropriate name as SendFilesToFTPServer.dtsx.

Lets take FTP Task as we want to send file from local system to FTP Server.

Go to Toolbox pane, select and drag FTP Task component into Control flow as shown below.

Now we will configure a FTP connection manager, double click on FTP Task to configure the FTP connection manager.

Note: The FTP connection manager supports only anonymous authentication and basic authentication. It does not support Windows Authentication.

Now you will see a FTP Task Editor window opens.

In General tab, you can see following options:

FtpConnection
Select an existing FTP connection manager, if you have already created one else click <New connection…> to create a connection manager.

StopOnFailure
It indicate whether the FTP task terminates if FTP operation fails.

Name
Provide a unique name for the FTP task. This name is used as the label in the task icon.

Description
Provide the description of the FTP task.

Lets provide FTP connection, click on dropdown select <New connection…>.

Next, a FTP Connection Manager Editor window opens, here you can see the the following options:

Server Name: Specify the FTP server name.
Server Port: Specify the port number on the FTP server to use for the connection. The default value of this property is 21.
User Name: Please provide the user name to access the FTP Server. The default value of this property is anonymous. By default, you can use the anonymous, but if you have FTP user name and password then provides user name of FTP Server.
Password: Please provide the password to access the FTP Server.
Time-out (in seconds) Specify the number of seconds the task takes before timing out. A value of 0 indicates an infinite amount of time. The default value of this property is 60.
Use passive mode: Specify whether the server or the client initiates the connection, The server initiates the connection in active mode, and the client activates the connection in passive mode. The default value of this property is active mode.
Retries: Specify the number of times the task attempts to make a connection. A value of 0 indicates no limit to the number of attempts.
Chunk size (in KB): Provide a chunk size in kilobytes for transmitting data.
Test Connection: Click to verify whether the connection is created properly or not.

Once you done with this, click on OK button.

Now go to File Transfer tab, you will see following options.

IsLocalPathVariable: Indicate whether the local path is stored in a variable, it has two option either True or False.

True means the destination path is stored in a variable. Selecting the value displays the dynamic option, LocalVariable.

False means the destination path is specified in a File connection manager. Selecting the value displays the dynamic option, LocalPath.

Operation: You can select the FTP operation to perform, list of all the predefined operations you can see in the beginning of this article.

IsTransferASCII: Indicate whether files transferred to and from the remote FTP server should be transferred in ASCII mode.

IsRemotePathVariable: Indicate whether the remote path is stored in a variable, It has two option either True or False.

True means the destination path is stored in a variable. Selecting the value displays the dynamic option, RemoteVariable,.

False means the destination path is specified in a File connection manager. Selecting the value displays the dynamic option, RemotePath.

OverwriteFileAtDest Specify whether a file at the destination can be overwritten or not.

Now select IsLocalPathVariable as False (By default selected), then define a LocalPath. You can select an existing local path, if you have already created else click on <New Connection…>.

Now select the file path then click on Open button.

Now you will see a file path is displayed in file textbox, that you can verify then just click on OK button.

Next, as we want to send the file, so the operation will be Send Files ( by default selected).

Now select IsRemotePathVariable as False (By default selected), then define the RemotePath, just click on ellipsis (…), a Browse for Directory window opens.

Just select the Directory where you want to upload file in FTP Server.

You can see here we have selected DataFiles directory, then click on OK button.