Click here to Skip to main content
15,845,744 members
Articles / Programming Languages / SQL

FTP directly in SQL (Using SQL CLR)

Rate me:
Please Sign up or sign in to vote.
4.60/5 (7 votes)
6 May 2011CPOL3 min read 49K   2.6K   18   6
Using FTP via CLR in SQL


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:
    FROM 'c:\Windows\System32\sqlFTP.dll'
  5. Create the CLR Stored Procedure:
    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

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.


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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer
South Africa South Africa
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionThank you - 1 suggestion Pin
J.W. Irvin29-Aug-17 5:50
J.W. Irvin29-Aug-17 5:50 
SuggestionOnly a small issue Pin
Member 977766116-Apr-14 8:19
Member 977766116-Apr-14 8:19 
GeneralYes sir, we did benefit Pin
jammy_VB.net10-Apr-14 6:22
jammy_VB.net10-Apr-14 6:22 
QuestionFTP transfer to SQL Pin
developer.cz25-Oct-11 23:38
developer.cz25-Oct-11 23:38 
GeneralGreat article! Pin
Virat Kothari8-May-11 21:43
Virat Kothari8-May-11 21:43 
GeneralRe: Great article! PinPopular
Justin Porteous11-May-11 21:45
Justin Porteous11-May-11 21:45 
Thank you very much
- Artificial intelligence is no match for natural stupidity

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.