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

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. With some modifications, you can use the example also for the Rebex SFTP component.

Methods of Rebex FTP API cannot be accessed from T-SQL directly because only static methods can be mapped to stored procedures. Because Rebex FTP API is not based on static methods, a wrapper class should be created to call Rebex FTP API.

NOTE: the following example considers using of “Rebex FTP for .NET” component in MS SQL Server 2008 and using MS Visual Studio 2008. See another article describing the same for SQL Server 2012+ and Visual Studio 2012+. CAUTION: you cannot put the "Rebex FTP for .NET CF" libraries (as well as any other "CF" libraries) into MS SQL Server.

Configure SQL Server and the database for CLR

Some of these initial steps can be skipped if the environment is already configured.

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

CREATE ASSEMBLY [Rebex.Net.Ftp]
 -- replace with your path to the Rebex.Net.Ftp.dll:
 FROM 'c:\Program Files (x86)\Rebex  FTP for .NET 2.0 Trial\bin\Rebex.Ftp.dll'
 -- "UNSAFE" permission set is needed to allow network communication
 WITH PERMISSION _SET = UNSAFE;

Note that importing of the Rebex.Ftp assembly also imports all other assemblies the primary assembly depends on. In our case, the Rebex.Common and the Rebex.Networking assemblies are automatically loaded.

You should see that two assemblies in the SQL Server Management Studio:

Write a wrapper class in Visual Studio 2008

Create a SQL Server Project in Visual Studio

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

After 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.

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.

NOTE: the list of assemblies is retrieved from the assemblies registered in the SQL Server Database (defined in the previous step), they can not retrieved from filesystem or GAC. The assemblies in the SQL server work in the separate subset of .NET framework and cannot see any assemblies outside.

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

Add a new project file “FtpUploadText.cs” with type “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 Rebex components
        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

1. The simplest way is to right-click on the project name in the
   Visual Studio and choose “Deploy”.

   

   In that case, don’t forget to set the “Permission level = Unsafe” first in the "Database" tab of project properties:

   

   1. for a manual deploy, use the following SQL command:

      CREATE ASSEMBLY [SqlServerProject1] 
      -- replace with your path to the compiled binaries 
      FROM 
      'c:\projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll' 
       -- "UNSAFE" permission set is needed to allow network communication 
       WITH PERMISSION _SET = UNSAFE;
      

Create and use the stored procedure

Map the CLR method to the T-SQL stored procedure

If you choose the "Deploy" operation in previous step, the stored procedure has been created automatically.

OR to call the method from the T-SQL, you should 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 that 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.test.com', 'auser', 'thepassword', '/folder/file.txt', 'Hello world!'

The article has been updated on 3rd May 2012:

• a support of SFTP has been mentioned
• the examples have been migrated to compatibility with 2012 R1
• the automatic and the manual deploy process of an assembly have been distinguished
• an instruction for using a trial version has been added.