Secure FTP File Transfer in SSIS

Unfortunately, there are no native components in SSIS to support the secure (SFTP) transfer of files. This video will demonstrate an effective workaround use a free Secure FTP program.

Tim Mitchell is a Microsoft SQL Server database developer, business intelligence consultant, writer, and speaker. He has been working professionally with SQL Server for over six years, and holds the MCTS and MCDBA certifications from Microsoft as well as a Bachelor's degree in computer science from Texas A&M University - Commerce.

Dav on 5/28/2009

please provide a link to the psftp executable

Tim Mitchell on 6/3/2009

Dav, here's a link to the PSFTP executable: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

Frank Luke on 6/3/2009

thanks, great job

Stephen Oesterreicher on 7/14/2009

Good news flash ...

Gina Meronek on 7/14/2009

Very useful info!

Gina Meronek on 7/14/2009

Supporting code link does not work, however.

Norma Hutchinson on 7/14/2009

I really needed this. Thanks!

Mr Jezba on 7/14/2009

Fantastic

Jamshid Nouri on 7/14/2009

excellent demo

Mark Redfern on 7/15/2009

where do you get psftp from?

manish on 7/15/2009

was good video

Brian K McDonald on 7/15/2009

Mark, read above and the link is provided. :)

995751D667 on 7/15/2009

terrific, and straight-forward

Travis on 7/16/2009

Just curious, why can't you use the SSIS FTP task already in SQL Server 2005 & 2008 instead of your third party tool? It appears to do the same thing, plus the 2008 version allows you to run in passive mode too which deals with firewall filtering..

Dinesh Pendlimarri on 7/17/2009

Very nice.

ANITA on 7/17/2009

Very detailed explation of ftp process.

Tim Mitchell on 7/17/2009

Travis, you're correct that the FTP task in SSIS works very well, but it does not support SFTP (secure FTP) connections. This video demonstrates a workaround for that.

Kristeen Sellers on 7/17/2009

The commentator was a little quiet. I had the volume all the way up and had difficulty hearing him.

TechyGirl on 8/4/2009

good video. nice job!

Law on 9/3/2009

nice to know that

Tonci on 10/14/2009

Great! I guess this works each time you need to run a program from shell, command line, command prompt, etc.

Harish on 12/3/2009

Can't believe people still complain even though going through from such a good demo. Grow up Kristeen

manohar on 12/29/2009

excellent !

Scott Stauffer on 1/15/2010

If you want to get the output try calling "cmd.exe /c PSFTP.exe {PARAMETERS} > DirectoryOutput.txt" THis will allow you parse the remote dir and drop it in a database . Now add compare logic and determine if a file transfer is still in progress from the person dropping the file for you to pick up. Do this by grabbing list, wait 5-10 minutes, if file size changed, then file is still in progress, wait longer... If the drop is delayed have it send and email to your service provider telling them they have an SLA issue, and they will be penalized :)

Charlie Bruno on 2/23/2010

learned something new....

Ramgopal on 3/5/2010

nic one

Darcy on 3/25/2010

Very explicit. Very helpful.

Arun on 4/1/2010

Good video

Todd Burrell on 5/19/2010

I've seen screen shots of using psftp on another site, but the video helps explain it a lot more. Now all I have to do if figure out to how specify variables/expression for the file names to send since they are dynamic (date dependent) rather than using the ans files in the demo.

Anjali on 6/9/2010

hi this is working fine. but when i deployed this in sql server 2005 and try to run it from sql server job it hangs. Please suggest what to do?

karan on 6/14/2010

its very good and helpfull for the DBA

jliveston on 6/17/2010

Great