May 15, 2007 at 5:06 pm
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
Ben Sullins
bensullins.com
Beer is my primary key...
May 15, 2007 at 7:21 pm
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
May 16, 2007 at 10:39 am
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
Ben Sullins
bensullins.com
Beer is my primary key...
May 24, 2007 at 9:45 am
Use an execute process task with PSFTP - no need to shell out $$$ for /n software's SSIS task.
Tommy
Tommy
Follow @sqlscribeDecember 6, 2007 at 3:36 pm
I am using WinSCP with a saved session and a script file and it works fine
December 19, 2007 at 9:53 am
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?
December 20, 2007 at 11:16 am
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.
December 20, 2007 at 11:44 am
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
December 20, 2007 at 12:37 pm
Thank you so much guys. I will check with it.
February 22, 2008 at 3:01 pm
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.
March 7, 2008 at 8:21 am
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
March 12, 2008 at 8:38 am
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.
April 10, 2008 at 4:55 pm
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
July 2, 2008 at 2:48 pm
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?
August 27, 2008 at 2:27 pm
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???
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply