SSIS FTP Connection Manager

The SSIS FTP Connection Manager is used to establish a connection with the File Transfer Protocol server, or say FTP Server. This Connection Manager not only allows us to access the existing remote files and folder but also creates New files and folders at run time in SQL Server Integration Services.

Configuring SSIS FTP Connection Manager

In this example, we show how to create or configure SSIS FTP Connection Manager.

Once you created a new Project under SSIS, If you look at the Solution explorer, we have three folders. For now, let’s concentrate on the Connection Managers Folder.

SSIS FTP Connection Manager 1

Right-click on the Connection Managers folder present in the solution explorer and select the New Connection Manager option from the context menu.

SSIS FTP Connection Manager 2

When you click on the New Connection Manager option, an Add SSIS Connection Manager window form will open to select the connections managers from the list it provides.

SSIS FTP Connection Manager 3

Here, we selected the SSIS FTP Connection Manager from the available list. Once we chose the FTP connection manager, another window form called FTP Connection Manager Editor opened to configure the connection.

SSIS FTP Connection Manager 4

The SSIS FTP Connection Manager provides the following options:

  • Server Name: Please specify the Company FTP server name here. For example, it will be something like ftp.tutorialgateway.org
  • Server Port: Please specify the Port number here. The client can access your FTP server using this available port (should be Open). By default, it will be 21
  • User Name: Please provide the user name to access the FTP Server. By default, you can use the anonymous, but in real-time, Your organization will provide these credentials or use the credentials you specified while configuring your FTP server.
  • Password: Please provide the password to access the FTP Server.
  • Use passive mode: If you uncheck this option (which is the default), FTP Connection Manager will use Active mode otherwise, Passive mode.
  • Retries: Please provide the number of retries. If it reaches maximum retries, Connection will Fail.
  • Test Connection: Click on this button to check whether our connection is successful or not.

NOTE: If the connection is using active mode, the Server will initiate the connection. Otherwise (Passive Mode), the Client has to start the connection.

SSIS FTP Connection Manager 5

From the above screenshot, you can observe that we are using the localhost (Our local PC) as FTP Server and a custom port number. In real-time, you have to select the Company’s FTP Server and provide the valid credentials given by your Admin person.

Let us test our established connection by clicking the Test Connection button. From the below screenshot, you can observe that Our credentials are Valid.

SSIS FTP Connection Manager 6

Click the OK button to finish configuring the SSIS FTP Connection Manager.

SSIS FTP Connection Manager 7

From the above screenshot, you can observe that we successfully created SSIS FTP Connection Manager.