Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to integrate with FTP and list, upload, and download files using CLR (C #)

Reading Time: 15 minutes

Hello people,
Good afternoon!

In this post, I will talk again about CLR (C #), demonstrating yet another usefulness of this powerful feature of SQL Server, which is the integration between database and FTP servers, either to download or upload files between a local or network folder and the FTP server.

This feature is especially useful when you need integrations with other companies, where you need to export data and make it available on an external FTP from another company or import files from an FTP and import to your network or database.

To return alert messages and CLR error messages to the database, I use the Return class, which is available in the post. SQL Server - How to send warnings and error messages to the bank through CLR (C #) and is a dependency of this FTP class.

In all routines, I put a "retry" of up to 10 attempts in cases of connection failure or timeout, so that your routine does not show failure due to any instability in the network (this has helped me a LOT).

If you do not know the CLR or do not know where to start, see more accessing the post Introduction to SQL Common Language Runtime (CLR) in SQL Server.

Base Class - FTPControle.cs

View more details, usage examples, and source code
This FTPControl class is the base class for this post, as all Procedures and Functions in this post need this class to function. The idea is to put all the logic of FTP control in this base class, and the procedures and functions only use the methods of this class.

Although the code is quite large, it is not a complex class, and can be easily understood.

How to List Files from an FTP by SQL Server

View more details, usage examples, and source code
Through the stored procedure below, it is now possible to list all files and directories present on an FTP server. This information will be very useful for identifying if a file was actually sent to the server or even for listing the files that exist in a directory for later download.

The filter parameter allows you to inform the name of the file that you want to filter or use a Wildcard to return only the files that meet the informed criteria (Ex: “*” returns all files, “* .txt” returns all files ending with. txt, etc.)

Example of use:
microsoft-sql-server-clr-integration-ftp-list-files-and-directories

Source code:

Function for listing files from an FTP by SQL Server

View more details, usage examples, and source code
Although you have already shown how to list files, this table-valued function allows you to identify a host of additional information about FTP server files and directories, such as creation date, size, permissions, and so on.

In addition, it can be easily used to filter with WHERE, use JOINS, ORDER BY, SELECT * INTO #Table, etc., giving much more flexibility in its use compared to the previous Stored Procedure.

Example of use:
microsoft-sql-server-clr-integration-ftp-function-list-files-and-directories

Source code:

How to download a file from an FTP server through SQL Server

View more details, usage examples, and source code
Using this Stored Procedure, you can easily download a specific file or multiple files using regular expressions, as I will demonstrate below.

An especially useful parameter is @apagarRemoto, which when the value “1” is informed, downloads the files that meet the criteria of the informed filters and, immediately afterwards, deletes these files from the FTP server.

Examples of use:

Downloading the "about.php" file

Downloading the “about.php” file

Downloading Multiple Files Using LIKE

Downloading Multiple Files Using Wildcard Filters

End result with files downloaded from FTP

End result with files downloaded from FTP

Source code: