FTP SQL Server Integration: 2 Easy Methods

This article aims at providing you with a step-by-step guide to help you set up the FTP SQL Server integration with ease to help you transfer your data to SQL Server for a fruitful analysis securely. Upon a complete walkthrough of the content, you’ll be able to connect FTP Server to SQL Server easily. It will further help you build a customized ETL pipeline for your organization. Through this article, you will get a deep understanding of the tools and techniques & thus, it will help you hone your skills further.

Introduction to FTP

File Transfer Protocol commonly known as FTP is a network protocol to transfer files between computers. The client-server model is used to build this protocol. FTP can be used for performing different operations but mainly it is used to send (known as put) and receive (known as get). FTP is a robust and fast network protocol that allows large file transfers based on a client-server model. Features like block storage, Storage Area Network, and NAS, etc can be used to optimize file transfer and storage strategies.

More information regarding the FTP [File Transfer Protocol] can be found here.

Introduction to Microsoft SQL Server

Microsoft SQL Server is one of the most widely used database systems and is used to manage and store information developed by Microsoft. It also caters to the needs for other very important operations as well including analytics operations, business intelligence operations, and transaction processing. Apart from just a database, it is a complete management system. 

SQL is the abbreviation of Structured Query Language, which is a computer language that manages and administers the server. More information regarding the Microsoft SQL Server can be found here.

Pre-Requisites

• Microsoft SQL installed at the host workstation.
• Working knowledge of Microsoft SQL Server.
• Working knowledge of FTP [File Transfer Protocol].
• A Microsoft SQL Server database.

2 Methods to Implement FTP SQL Server Integration

• Method 1: FTP SQL Server Integration using SSIS FTP Connection Manager
• Method 2: FTP SQL Server Integration using Hevo’s No-code Data Pipeline

Method 1: FTP SQL Server Integration using SSIS FTP Connection Manager

Introduction to SSIS

SQL Server Integration Services, also known as SSIS is one of the most important components of the Microsoft SQL Server database software that performs a broad range of data migration tasks. It is not just a platform for data integration but also provides application workflows. It also features a data warehousing tool used for a complete ETL pipeline i.e. Extract Data, Transforms it & Load data as well.    

One of the most important advantages of SSIS is that it provides a graphical interface so one doesn’t have to write even one line of code to perform all the functionalities. But still provides you the feature to write custom code & scripts if needed to fulfill your requirement. 

Introduction to SSIS FTP Connection Manager

The SSIS FTP connection manager is a package that connects to a File Transfer Protocol (FTP) Server. The FTP task that SQL Server Integration Services includes uses this connection manager. You can also read about FTP s3.

When an FTP connection manager is added to a package, Integration Services creates a connection manager that provides an FTP connection at run time. Along With providing an FTP connection, it performs two more operations as well:

• Sets the connection manager properties.
• It enables the connection from the Connection Manager to the Connections collection.

Procedure to Implement FTP SQL Server Integration

To configure the FTP connection manager following parameters need to be mentioned:

• Firstly, you need to mention the server name and server port.
• If a username & password is available, provide it. Otherwise, you can keep it anonymous.
• You need to mention FTP connection manager mode as well i.e. active or passive mode.
• The last thing to do is to provide a time-out, amount of data to copy at a time, and a number of retries.

To add this connection manager, click on the “New connection” button as shown below. This menu appears as you right-click in the section. 

It will show you a list of different Connection Managers. From the Connection Managers list, you need to choose the FTP connection manager. Also shown in the image below:

As you select the FTP connection manager, the connection manager form appears. 

As a demonstration, we can create an FTP site on port 21 which will read from a local folder without requiring permission. Following image shows the setting:

Therefore, the overall FTP connection manager has the following parameters:

• Server Name: The FTP Server name (example: ftp.anyname.com).
• Server Port: The port number being used.
• User Name: The username which if set up can be used for authentication.
• Password: The password is also used for authentication.
• Use passive mode: Selection of active or passive modes for connection.
• Retries: Number of retries that are allowed to establish a connection.
• Test Connection:  To check whether the connection has been successful or not.

More about the FTP connection manager on the Microsoft official documentation.

SSIS FTP Task 

SSIS FTP task performs the downloads and uploads functionalities and also manages folder directories on servers. FTP Task can be used for one of the following or all purposes:

• To Copy the data files from one folder to the other, and also applying transformations to the data as well.
• To Copy files or packages to the different folders or directories by logging.
• To download from an FTP location and apply transformations to column data before sending data to the database.

To read further about FTP Tasks, you can go through the Official Documentation. 

As the FTP connection manager has been set up, you are now able to download any FTP Task and upload data and manage folders/directories on the FTP Server. 
As the image shows that FTP Task is defined as “Works with FTP Servers. Manage directories or download and upload data files. For example…. ”.

As the image below shows, there are three tabs in the SSIS Task editor:

General Tab

In this Section or Tab, the information about Connection Status, General Information (Name & the description of the Task is explained) & also the StopOnFailure to indicate if there has been an issue with the operation. 

File Transfer Tab 

In this tab, the configuration is performed. As shown in the image below, there are different operations that can be performed including Sending files, Receiving files, Creating a local directory, Creating a remote directory, Removing local directory, Removing remote directory, Deleting local files, Deleting remote files, and Deleting files from the FTP Server.

Each of these operations possesses its own configuration as well which can be modified.

Expressions Tab

This tab is used to evaluate the SSIS FTP Task properties. 

Moving onto the data transfer part, we will transfer data (files) from the local machine to the FTP Server. Send Files operation is used for that purpose. Following Parameters need to be set:

Local Parameters: These parameters are related to the files that are being uploaded:

• IsLocalPathVariable: This specifies if we need to read the local path from an SSIS variable.
• LocalPath: This variable specifies the local file path. if it is set to true then we SSIS variable needs to be selected otherwise we need to select a File Connection Manager.

Operation: These parameters are related to an FTP operation:

• Operation: This is the simple operation type.
• IsTransferASCII: This parameter represents if the transferred file to and from the remote FTP Server should use ASCII mode or not.

Remote Parameters: These are related to the FTP Server i.e. destination:

• IsRemotePathVariable: This specifies if there is a need to read the destination path from an SSIS variable.
• RemotePath: This specifies the destination file path which is set to true, chooses the default path, or else it needs to be entered manually.

OverwriteFileAtDest: This can be selected if you want to overwrite the existing file. Otherwise don’t choose it.

The Configuration has been completed. By allowing the package, the transfer of the file gets completed.

Congratulations your FTP SQL Server Integration has been set up!

Limitations of using SSIS FTP Connection Manager for FTP SQL Server Integration

• Requires significant time investment and technical knowledge to implement.
• Potential errors with manual implementation could lead to data loss.
• Lack of an update process could lead to incompatibility in long run.

Method 2: FTP SQL Server Integration using Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline, helps you transfer data from FTP to SQL Server in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Since Hevo is completely managed, your data projects can take off in just a few mins. Here’s how simple it is to move data from FTP to SQL Server:

Hevo Data focuses on two simple steps to get you started:

• Configure Source: Connect Hevo Data with your FTP Server by providing a unique name for your pipeline along with information about the type of server you want to with. You will further need to provide your credentials such as username and password to allow Hevo access to it, along with details about the associated port and host values. You can also specify the type of file you’ll want to transfer, choosing between XML, JSON, and CSV. In case you want to connect using SSH, you can also enable the option for the same.

• Integrate Data: Load data from FTP Server to Microsoft SQL Server by providing a unique name for your destination along with your database credentials such as username and password. To help Hevo connect with your Microsoft SQL Server database, you will also have to provide information such as the host IP, port number, and the name & schema of your database.

Conclusion

This article teaches you how to set up the FTP SQL Server integration using 2 methods: manual integration using SSIS FTP connection manager or using Hevo’s no code data pipeline.

Extracting complex data and manually using SSIS FTP Connection manager is a manual process and could require significant time and engineering efforts. This can be quite challenging and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications such as FTP into your destinations like SQL Server to be visualized in a BI tool. Hevo is fully automated and does not require manual intervention.

FAQ on FTP SQL Server Integration

Want to take Hevo for a spin? Signup for a 14-day free trial and experience the feature-rich Hevo suite first hand.