In SQL Server Integration Services, there is a FTP Task you can use to upload and download files to or from, respectively, a FTP site.  If you need to upload or download from a SFTP or FTPS site, you’ll need Ivan Peev’s SFTP Task from CozyRoc (www.cozyroc.com).  I’ll have a lot more to say about Ivan and his superb suite of tools for SSIS later.  The issue we are talking about today is showing some common courtesy to the owner of the FTP site via a way you probably haven’t thought of.

A few months ago, I got a call from Joel Hoover who maintains the FTP site for RealMed, our clearinghouse we use to send electronic claims through (www.realmed.com).  Joel informed me that, according to his logs, when we downloaded or uploaded files to his site, we were making upwards of almost 500 connections per day.  After doing some quick math, I realized that we should, at most, be making about 6 connections per day since we only download or upload files from their site 6 times a day, so where did he get 500 from?  I informed him that this must be some sort of mistake but then he emailed me a screenshot of the logs with concrete evidence that, yes, we were connecting to this site around 500 times per day – sometimes more.  I did some digging and found the culprit. 

In SQL Server Integration Services, each Task that executes something against a data source must have a Connection Manager.  The FTP Task is no exception.  It has a FTP Connection Manager (or SSH if you are using Ivan’s tool).  And, like most of the other Connection Managers, it has configurable properties – and I’m betting some of you know where I’m going with this already.  Here is a screenshot:

Notice the properly value RetainSameConnection.  By default this is typically set to FALSE.  You have to remember to set it to TRUE.  So, what was happening is that as I looped through all the files on Joel’s FTP site in my SSIS package, I was constantly:

Open connection
perform some operation
Drop connection
open connection
perform some operation
drop connection
open connection
perform some operation
drop connection

You can probably see how the FTP owner, Joel, was freaking out over this.  By changing that one value, I was able to reduce our number of connections from well over 500 down to about half a dozen for the entire day.  By setting the value of RetainSameConnection to TRUE, the first connection made to the FTP site is retained for all future uses of that Connection Manager throughout the life of the SSIS package.

In fact, I would say that, unless you are changing your connection string in your connection manager (i.e. looping through csv files, for example), I would recommend always changing the value of RetainSameConnection to TRUE.  This will reduce the overall overhead of your SSIS package and not hammer your data source so much.

James