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

Views: 2.114
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:

How to send a file to an FTP server through SQL Server

View more details, usage examples, and source code
Using this Stored Procedure, you can upload files from your local server or files that are on network paths to an FTP server.

Again, SP lets you specify the file name in the @filter parameter to send a specific file, or use wildcards to perform filename filtering and send multiple files in a single Stored Procedure run.

Example of use:

Uploading all files with extension ".txt" to the FTP server

Uploading all files with extension “.txt” to the FTP server

Source code:

How to create a directory on an FTP server through SQL Server

View more details, usage examples, and source code
This Stored Procedure is intended to allow the creation of directories on an FTP server. This is very important, because if you need to upload files to a new directory, informing that directory when uploading without first creating it will cause an error message.

Using the functions of listing files and directories, you can identify whether or not the desired directory exists, and if not, create it using this SP.

Example of use:
microsoft-sql-server-clr-integration-ftp-how-to-create-directory

Source code:

How to Delete a File from an FTP Server by SQL Server

View more details, usage examples, and source code
Stored Procedure that allows you to delete one or more files from an FTP directory. Unlike previous SP's, it is not possible to use a filter to delete multiple files. The parameter @arquivoRemoto must receive the name of the file that will be deleted. If you want to delete several files, use a WHILE to iterate between the files and call the SP several times.

This is not due to technical impossibility, I just found it more “safe” not to allow deleting multiple files using a filter, but nothing to stop you from changing this Procedure to allow this or using the Stored Procedure stpFTP_Apaga_Diretorio, which is just below.

Example of use:
microsoft-sql-server-clr-integration-ftp-how-to-delete-files

Source code:

How to Delete a Directory from an FTP Server by SQL Server

View more details, usage examples, and source code
Using this Stored Procedure, you can delete directories from your FTP server. A detail this SP, is the parameter @apagaArquivos, which if you enter the value 1, will delete all files in this directory and then try to delete the directory.

Keep in mind that if you enter the value of the @appileFiles = 0 parameter and there are files in the directory you are trying to delete, SP will return an error message stating that there are files in the directory.

Example of use:
microsoft-sql-server-clr-integration-ftp-how-to-delete-files-and-directory

Source code:

That's it folks!
I hope you enjoyed this post and even more!

sql server clr C # csharp download download read read upload upload files files list list integrate integrate integration integration server ftp server

sql server clr C # csharp download download read read upload upload files files list list integrate integrate integration integration server ftp server