How to upload data to FTP or SFTP server from a SQL-CLR procedure (SQL Server 2012 and VS 2012)

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.

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 store following three assemblies somewhere on your : Rebex.Ftp.dll, Rebex.Common.dll, and Rebex.Networking.dll. Only the primary Rebex.Ftp.dll is imported to SQL:

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;

The dependent assemblies Rebex.Common.dll and the Rebex.Networking.dll are automatically loaded.

You would check that in the SQL Server Management Studio:

check-assemblies

Write a wrapper class in Visual Studio 2012

Create a SQL Server Project in Visual Studio

Create new VS project of type “SQL Server Project” (located in the “database” section):

New Project

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.

DatabaseProjectError

Add reference to the Rebex assemblies

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

addReference

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”:
addNewItem

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 to ask Rebex support (support@rebex.net) for a one-purpose, time limited trial key. 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.