I have a requirement to upload 100+ files daily to a local FTP site and I wanted to control the process via SQL Server. I started with Command Shell option and that works fine but is very short on error handling/sophistication. My next thought was to write a CLR and execute as a stored procedure. The example i found online used FtpWebRequest using System.IO and it worked fine, but as soon as I tried a 500 MB file it bombed on a escalation policy memory error. This is on a 64 bit server with 128 GB of memory (96 GB max for SQL Server 2008 R2 upgrading to 2012 this weekend). That led me to an SSIS solution. I have read that using a script task is a better performer than an FTP task and have the script task option working using FtpClientConnection class. Is that the best option? There are some missing features with this class and wanted to find some best practices advice from someone much more familair SSIS/FTP than myself. Thanks for any help that you can offer. RJ

If uploading a large number of files as fast as possible is the paramount in your solution then consider uploading them in parallel, if by using the SSIS canned FTP Task then you can have several DFTs each picking its own set of files based on say a date range or some other dividing factor. I am leaning toward telling you that no one method of FTPing is faster than another because a remote file transfer it more network and/or remote resources dependent then local (very little CPU is used to send files). The local disk speed may be a factor, but to a lesser degree. And as to why you had this error that is strange. So back to what I wanted to say: consider FTPing files from SSIS in a loop and in parallel, or the same using your script component.Arthur My Blog

That was my guess as well but I'm always happy to "ask for directions" from those more knowledgeable. Thanks for the feedback.

Can you share your resoultion. I have something similar I am trying to accomplish but with SSIS