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.
Create a Visual Studio Project as a Class Library.
Decorate functions to be exposed with [Microsoft.SqlServer.Server.SqlFunction].
Compile Project in and copy .DLL file to the appropriate folder.
Register Assembly from within SQL Server Management Studio:
CREATE ASSEMBLY clr_sqlFTP AUTHORIZATION dbo
FROM 'c:\Windows\System32\sqlFTP.dll'
WITH PERMISSION_SET = UNSAFE
Read more: Codeproject