How to upload data to FTP or SFTP server from a SQL-CLR procedure

Since Microsoft SQL Server 2005, stored procedures (and some other database objects) can be written externally in some .NET language, imported to the SQL database and called as stored procedures. The following sample demonstrates how to call Rebex FTP component from the T-SQL code in the environment of SQL Server 2012+ and Visual Studio 2012+. This blog connects to the previously published blog How to upload data to FTP or SFTP server from a SQL-CLR procedure that deals with the same topic but for SQL Server 2008 and Visual Studio 2008.

+ Steps described in this article work also for SQL Server 2016, SQL Server 2017 and SQL Server 2019 as well as for Visual Studio 2017 and Visual Studio 2019.

The sample is demonstrated on Rebex FTP component, but you can analogously use it for the Rebex SFTP, or the other component. Note that the sample considers using Rebex FTP for .NET platform (you can not put an assembly for e.g. .NET CF platform).

Only static methods can be mapped to SQL stored procedures. Since Rebex FTP API is not based on static methods we cannot access them from T-SQL directly. We suggest a wrapper class to overcome this problem.

Configure SQL Server and the database for CLR

Enable CLR in the SQL Server

USE master 
GO 
sp_configure 'clr enabled', 1 
GO 
RECONFIGURE
GO

Create a sample database

CREATE DATABASE Test 
GO

Make the database trustworthy to allow access external resources

USE Test 
GO 
ALTER DATABASE Test SET TRUSTWORTHY ON 
GO

Import the Rebex assemblies into the database

For Rebex FTP transfer you need to have the following three assemblies in some directory on your SQL server machine:
Rebex.Ftp.dll, Rebex.Common.dll and Rebex.Networking.dll. We recommend to use libraries for platform net-4.0 or maybe newer (see this article for more details).

Now create a SQL envelope over the primary assembly Rebex.Ftp.dll:
(the dependent assemblies Rebex.Common.dll and Rebex.Networking.dll will be automatically loaded)

CREATE ASSEMBLY [Rebex.Net.Ftp]
 -- replace <path> with your path to the dlls:
 FROM '<path>\Rebex.Ftp.dll'
 -- "UNSAFE" permission is needed because the component uses some static fields
 WITH PERMISSION_SET = UNSAFE;

You would check that in the SQL Server Management Studio:

Write a wrapper class in Visual Studio

Create a SQL Server Project in Visual Studio

Create new VS project of type “SQL Server Database Project”
(the dialog window looks differently in specific versions of Visual Studio):

During creating the project, you will be prompted to add a database reference. Specify connection to the database where the Rebex assemblies were imported in the previous step.

Note: If you get following error about incompatibility probably this update can help you.

Add reference to the Rebex assemblies

In the Visual Studio project References click on the “Add Reference”. The following dialog should appear:

Select Rebex.Common, Rebex.Ftp and Rebex.Networking assemblies and click OK.

Create a new method for the .NET code of the stored procedure

Add a new project file “FtpUploadText.cs” with type “SQL CLR C # Stored Procedure”:

NOTE: the Visual Studio’s SQL Server Project stores all procedures to a one big partial class – each method into a separate .cs file.

Implement body of the stored procedure method

Let’s create a following sample method that receives a string and writes it into a text file on the FTP server:

using System;
using Microsoft.SqlServer.Server;
using Rebex.Net;
public partial class StoredProcedures
{
    /// <summary>
    /// Upload a text to the file on FTP server.
    /// </summary>
    /// <param name="ftpHost">Hostname of the FTP server.</param>
    /// <param name="username">FTP user name.</param>
    /// <param name="password">FTP user password.</param>
    /// <param name="remotePath">FTP path of the target file.</param>
    /// <param name="content">
    ///   String that will be stored into the target file.
    /// </param>
    [SqlProcedure]
    public static void FtpUploadText(string ftpHost, string username, 
    string password, string remotePath, string content)
    {
        // the following line is needed only for a trial version of components
        // you can obtain it on the following address:
        // http://www.rebex.net/support/trial-key.aspx
        Rebex.Licensing.Key = "== a trial key obtained from Rebex support ==";
        // create client, connect and log in
        using (Ftp client = new Ftp())
        {
           client.Connect(ftpHost);
           client.Login(username, password);
           // upload a text using a MemoryStream
           byte[] data = System.Text.Encoding.Default.GetBytes(content);
           System.IO.MemoryStream ms = new System.IO.MemoryStream(data);
           client.PutFile(ms, remotePath);
        }
    }
}

TRIAL VERSION NOTE:

The expiration-date checking, performed in trial versions of Rebex components, fails within SQL-CLR environment. To evaluate the trial version, you have obtain an explicit trial key on Rebex website. Such key is no more needed when using the full (non-trial) version.

Build and deploy the stored procedure method

Build the project and import the compiled assembly into the SQL Server Database in Management studio:

CREATE ASSEMBLY [SqlServerProject1]
  -- replace <path-bin> with your path to the compiled binaries 
  FROM '<path-bin>\SqlServerProject1\bin\Debug\SqlServerProject1.dll'
  -- "UNSAFE" permission is needed because the component uses some static fields 
  WITH PERMISSION_SET = UNSAFE;

Create and use the stored procedure

Map the CLR method to the T-SQL stored procedure

Create a stored procedure envelope like this:

CREATE PROCEDURE FtpUploadText (
  @host NVarchar(1000),
  @username NVarchar(100),
  @password NVarchar(100),
  @remotePath NVarchar(1000),
  @content NVarchar(max)                ) 
AS 
EXTERNAL NAME [SqlServerProject1].[StoredProcedures].[FtpUploadText] 
GO

Syntax of the EXTERNAL NAME phrase is:

• [SqlServerProject1] – name of the assembly (as the assembly has been imported to the SQL Server database)
• [StoredProcedures] – class name
• [FtpUploadText] – method name

NOTE: The number and types of the stored procedure parameters should be the same as parameters of the method. Parameter names need not be the same.

Execute the stored procedure

The stored procedure can be executed as usual:

EXEC FtpUploadText 'ftp.someServer.com','user','password','/folder/file.txt','Hello world!'

Uploaded folder must exist; our procedure do not create one automatically.