March 12, 2010 at 4:55 pm
Whenever we have needed to make a secure FTP connection, we have used the following method:
1. Use an Execute Process Task to open cmd.exe (the Executable) and calls a .bat file (The Argument). The .bat file opens up PuTTY's psftp and references a .script file for passing commands within the ftp session.
2. A Send Mail Task which sends a custom error message to the DBA, should the package fail or time out (This e-mail basically spells out the process in detail and gives possibilities on why it may have failed, so that someone not familiar with the process isn't completely boggled).
The process has worked fine for us so far, but seems like a recipe for disaster because of all the dependencies (ftp client, .bat file, script file, and, of course, the requirement to accept a registry key on the server making the secure connection). I know that this method is used in many places as an acceptable, cost-effective alternative to buying a 3rd Party SSIS Task plug-in. To us, though, it feels like a "house of cards".
Does anyone know of Microsoft planning to include secure FTP connections (SFTP, FTPS, FTPES) as options in their FTP Task for future releases of SSIS? (We are currently on SQL Server 2005, but are making the transition to 2008 with a few databases). The only thing I have found is a request on Microsoft Connect to Add SSL Support (which only has 14 votes so far and was posted in 2006) https://connect.microsoft.com/SQLServer/feedback/details/246456/add-ssl-support-in-ssis-ftp-task
March 15, 2010 at 8:00 am
I have not seen anything for support for secure connections out of the box.
Although I haven't used it I hear CozyRoc.com has a component, you could also look on CodePlex for a free component.
CEWII
March 15, 2010 at 9:03 am
Thanks for the reply. Didn't find any free tasks on CodePlex, just links to the CozyRoc and software solutions for the secure ftp, which are "for fee" solutions.
March 15, 2010 at 9:12 am
I looked for SFTP solutions as well for a process we use. Every provider wanted you to buy a suite of packages which really wasn't (isn't) cost effective for us. Why pay for 10 to 14 packages when when we only needed one package, and had no use for any of the others.
March 15, 2010 at 9:25 am
Yep. Couldn't agree more.
March 15, 2010 at 10:06 am
Last place I was at we just used Putty and executed it in the job. Worked just fine. You do have to install Putty and the keys locally on the SQL server tho.
March 15, 2010 at 10:11 am
We found an inexpensive sslftp solution that we incorporated using an EXECUTE PROCESS TASK to handle our secure FTP task. Only requirement, we have to maintain that piece of the SSIS process on the server itself. It doesn't support FTP from a UNC.
March 16, 2010 at 8:25 am
I use WinSCP. It supports UNC paths.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply