FTP directly in SQL (Using SQL CLR)

• Download demo project - 4.83 KB
• Download source - 8.49 KB

Introduction

Working with a complex SQL environment which used a multitude of technologies, one of the problems we encountered was using SQL Jobs to transfer files via FTP.

The existing SQL Jobs in our environment used an external application (the command line application ftp.exe) called via the xp_cmdshell extended stored procedure, to transfer the files to the FTP servers.

It worked most of the time but when it didn't there was no easy way to trace the failures directly within SQL.

Transact-SQL can be powerful and fun to use, but it has some limitations and lacks the advantages of managed code.

The solution I've provided allows for better handling of code... Drum roll for CLR Stored Procedures...

CLR Stored Procedures

Defining a CLR Stored Procedure allows one to utilise managed code from within SQL. This gives almost infinite possibilities in extending the functionality of SQL Stored Procedures by leveraging the .NET Framework with your favorite language such as C# or VB.NET.

By using a CLR Stored Procedure which calls the function defined in the pre-compiled .NET DLL, we were able to transfer files via FTP to and from FTP Servers directly from a SQL stored procedure and catch and handle exceptions and finally return the results to SQL variables.

Leveraging this, we used a trace table to record when a file was successfully uploaded or downloaded and if not we recorded the error to assist with debugging/troubleshooting for the SQL Database Administrators.

This solution was inspired by the article CLR Stored Procedure and Creating It Step by Step which was written by Virat Kothari.

Getting Started

This is a high-level overview of the steps taken to create the CLR Stored Procedure.

1. Create a Visual Studio Project as a Class Library.
2. Decorate functions to be exposed with [Microsoft.SqlServer.Server.SqlFunction].
3. Compile Project in and copy .DLL file to the appropriate folder.
4. Register Assembly from within SQL Server Management Studio:
   SQL

   CREATE ASSEMBLY clr_sqlFTP AUTHORIZATION dbo
   FROM 'c:\Windows\System32\sqlFTP.dll'
   WITH PERMISSION_SET = UNSAFE 

5. Create the CLR Stored Procedure:
   SQL

   CREATE PROCEDURE ftpDownload
          @ftp_server nvarchar(MAX),
          @ftp_user nvarchar(MAX), 
          @ftp_pwd nvarchar(MAX), 
          @local_file nvarchar(MAX),
          @rmt_file nvarchar(MAX),
          @ftp_mode int,
          @result nvarchar(MAX) OUTPUT
    AS EXTERNAL NNAME clr_sqlFTP.[sqlFTP.FTP].ftpDownload

6. Execute the CLR Stored Procedure as per any normal Stored Procedure and save the results to a Trace Table.

Using the Code

I have provided the source and the compiled solution which will allow anyone who wants a quick fix or who wants to learn more about the solution to get started with CLR Stored Procedures and a FTP Solution which works directly within Microsoft SQL.

The FTP functionality is provided by the .NET class FtpWebRequest which inherits WebRequest.

By executing the CLR Stored Procedure and saving the results into a table, you can record the results of the upload or download.

Installing SQL FTP Solution

If you download the compiled solution, ensure you copy the .dll file to the c:\windows\system32 folder or modify the location of the file in the Create Assembly statement.

To enable CLR within a SQL database, use the following:

sp_configure 'clr enable', 1
GO
RECONFIGURE
GO
ALTER DATABASE <databasename> SET TRUSTWORTHY ON

Points of Interest

Problems don't create solutions, they create a will for solutions, people create the solutions.

I hope I've created a solution which you can benefit from.

History

This is the first version of this article and I welcome any information or advice to better the solution provided.