SSIS Secure FTP Task

Question

SSIS Secure FTP Task

Ben Sullins-437405

SSCertifiable

Points: 5397
More actions
May 15, 2007 at 5:06 pm

#118755

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...

Viewing 15 posts - 1 through 15 (of 46 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 2

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...

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 3

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

Tommy

Follow @sqlscribe

halexander SSC Enthusiast Points: 196 More actions · Answer 4

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

hint1979 SSC Enthusiast Points: 147 More actions · Answer 5

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?

mail-586892 SSC Journeyman Points: 87 More actions · Answer 6

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.

halexander SSC Enthusiast Points: 196 More actions · Answer 7

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

hint1979 SSC Enthusiast Points: 147 More actions · Answer 8

hint1979

SSC Enthusiast

Points: 147

December 20, 2007 at 12:37 pm

#761881

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

psyko_funk Valued Member Points: 59 More actions · Answer 9

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.

sbrandhoy SSC Enthusiast Points: 118 More actions · Answer 10

I'm just a newbie, myself, but it sounds like a problem I had where the SQLServerAgent did not have the proper permissions to read/write to a certain folder. When you run as debug or run the job yourself, it works because YOU have permission. When you schedule it, it doesn't

bander87 Valued Member Points: 57 More actions · Answer 11

I personally use a program called Putty so SFTP. This is not a problem with your package or batch file, but possibly a permission issue.

ManishJain Right there with Babe Points: 770 More actions · Answer 12

Hi,

Would you please tell me how getlisting of folder using this dll. because my file belong inside 3 sub folder and 2 folders name may anything...

like

Root

1

date1

Folder

File1

file2

date2

Folder

File1

file2

2

date1

Folder

File1

file2

date2

Folder

File1

file2

Can i get directory listing and make a loop for each directory. If any one know please share your knowledege...

Thanks

Manish Jain

mpothier SSC Journeyman Points: 96 More actions · Answer 13

I experience the same issue with PSFTP... session runs fine when executed interactively, by if I schedule a job, the PSFTP process hangs indefinitely. It is not a permission issue, as I've set the SQL Agent to run as me just to make sure. Does anyone have a solution to correct the apparent "Hang" issue?

shogg Mr or Mrs. 500 Points: 541 More actions · Answer 14

I have the same problem. At first I was sure it was the PSFTP registry entry required due to SSH. When you first execute PSFTP connection with a new site, it requires interaction. To save the server key or not. If you save the server key you will not get asked again.

I logged into the server, executed the PSFTP process and selected the option 'y' to save to registry. However, after this step the SQL Server Agent process still hangs. Puzzled???