SSIS Secure FTP Task

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS Secure FTP Task

47 posts, Page 1 of 5

1 2 3 4 5 »»»

SSIS Secure FTP Task

Rate Topic

Display Mode

Topic Options

Author

Message

Ben Sullins-437405

Posted Tuesday, May 15, 2007 5:06 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, October 25, 2010 10:10 AM
Points: 297, Visits: 267

I am needing to transfer data using an encrypted tunnel. The only way I know of how to do this is to use Secure FTP (SFTP). SSIS has an FTP Task but doesn't seem to have any support for SFTP.

Anyone run into this before?

Thanks,
Ben

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

Post #366150

Tim Mitchell

Posted Tuesday, May 15, 2007 7:21 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:11 PM
Points: 1,033, Visits: 2,593

Ben,

You are correct that the native FTP tools in SSIS will not support SFTP. You'll need to look at a third party utility like /n software (http://www.nsoftware.com/ssis/) for true support of SFTP. You might look at using something like PGP or other encryption tool to encrypt your file using a script or execute process task.

hth
Tim

Tim Mitchell
SQL Server MVP
www.TimMitchell.net
twitter.com/Tim_Mitchell

Post #366161

Ben Sullins-437405

Posted Wednesday, May 16, 2007 10:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, October 25, 2010 10:10 AM
Points: 297, Visits: 267

Thanks Tim,

I've seen the /n software SSIS utilities app but the price is way outside the range for this. I have found however the open source WinSCP tool that has a command line interface that can be used to complete this. I haven't completed this yet but after reading through their documentation believe it will work.

http://winscp.net/eng/docs/scripting

Cheers,
Ben

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

Post #366462

Tommy Bollhofer

Posted Thursday, May 24, 2007 9:45 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:43 PM
Points: 1,148, Visits: 3,148

Use an execute process task with PSFTP - no need to shell out $$$ for /n software's SSIS task.

Tommy

Post #368712

halexander

Posted Thursday, December 06, 2007 3:36 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 26, 2007 7:33 AM
Points: 2, Visits: 8

I am using WinSCP with a saved session and a script file and it works fine

Post #430480

hint1979

Posted Wednesday, December 19, 2007 9:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 01, 2010 10:46 AM
Points: 7, Visits: 61

Hi halexander

Can you paste code which you have written in SSIS Script task. I am in same situation just like you. Do I have to download and Install WINSCP first or any component is there?

Post #434854

mail-586892

Posted Thursday, December 20, 2007 11:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 08, 2012 7:59 AM
Points: 1, Visits: 23

I followed the previous post suggestion and used PSFTP (http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html).

Then I created a .bat file that looks like this:

cd "C:\Folder where you want the files to be put on your local client"
"C:\psftp" (host name of sftp server) -l (user) -pw (password) -b c:\sftpcopy.bat

Note - remove the brackets in the psftp command.

In c:\sftpcopy.bat I have:

mget *
quit

This assumes that your default server folder is where the files that you want to download. Otherwise you need to insert a CD command before the mget.

Then, in integration services, create a new package and insert a process task. Simply add the name of the .bat file in the executable process name.

Post #435329

halexander

Posted Thursday, December 20, 2007 11:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 26, 2007 7:33 AM
Points: 2, Visits: 8

The previous Post is accurate, however, I didn't want to put the required login and password into a file (not the best practice). So I installed WINSCP (http://winscp.net/eng/index.php) and created a Stored Session with all the connection specifics including a encrypted password.

I then created a text file into a directory on the machine and put the following text into it:

# Force Binary Transfer
option transfer binary
# Download all gzip and zip
get *.gz
get *.zip
# Close connection
close
# Exit WinSCP
exit

As you can see, I just wanted to download load files but you can perform any ftp operation you wish in this file.

As in the previous post I created an Execute Process task and put the path of my executable in the Executable parameter and put the arguments "(WinSCP session name) /console /script=(Fullpath WinSCP file)" in the Arguments

Just remember to put the download or upload directory in the WorkingDirectory parameter.

Also remember the () should be replaced by your specific names.

Hope that helps

Post #435340

hint1979

Posted Thursday, December 20, 2007 12:37 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 01, 2010 10:46 AM
Points: 7, Visits: 61

Thank you so much guys. I will check with it.

Post #435355

psyko_funk

Posted Friday, February 22, 2008 3:01 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 28, 2010 8:53 AM
Points: 1, Visits: 3

Every method I've tried for executing PSFTP from within a sql server 05 package does not work. It doesn't succeed or fail, it just simply gets to that process step and hangs indefinitely. my current method is an Execute Process task that runs a batch file, the batch file changes the directory to the working directory, uses a runas to my domain account, and then runs psftp with a batch. This method works if I run the package through visual studio debugger, if I install the package and run it myself from sql server, everything except when I actually schedule it to run from sql service agent.

Post #459331

« Prev Topic | Next Topic »

47 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions