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

Views: 2.103
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 have retryed up to 10 attempts in cases of connection or timeout failure, so that your routine will not fail due to any network instability (this has helped me MUCH).

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 - FTPControl.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 enter the file name you wish to filter or use a Wildcard to return only those files that meet the specified criteria (eg “*” 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 entering the value “1” downloads files that meet the criteria of the entered filters and then deletes those 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:

Sending all files with extension ".txt" to FTP Server

Sending all files with extension ".txt" to 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 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 @RemoteFile parameter should be given the name of the file to be deleted. If you want to delete multiple files, use a WHILE to iterate between files and call SP several times.

This is not due to technical impossibility, I just found it “safer” not to allow multiple files to be deleted using a filter, but nothing to prevent you from changing this Procedure to allow it or using the Stored Procedure stpFTP_Apaga_Directory 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