Monday, March 07, 2011 6:12 PM
Was I dreaming or did I see a post that said the latest version of SSIS now supports SFTP.
IF not I need to run a script in the script task that first creates the process:
winscp As Process = New Process()
but I need to set up the connection etc within the script as I would do with the regular ftp script code.
Any good examples - the example on the winscp site is vaque.
I've now got the ftp task inside of a ForEachLoop container. And I feed it a variable containing the next file in the directory which it now sends all the files in a directory.
Monday, March 07, 2011 6:25 PMModerator
There is no such SSIS version, but there are solutions.
First is your example to using the WinSCP:
Second, there are commercial components with all the bells and whistles e.g:
- Xceed: http://xceed.com/FTP_NET_Features.html
And a free one from CodePlex: http://ssissftp.codeplex.com/
Arthur My Blog
- Edited by ArthurZMVP, Moderator Monday, March 07, 2011 6:25 PM to add link to CozyRock SFTP
Monday, March 07, 2011 7:47 PM
I'd like to echo Arthur.... I ended up writing a script wrapper around psftp.exe (PuTTY client: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html).
It's better to use a 3rd party task in this case, if your budget allows.
Monday, March 07, 2011 9:49 PM
I'm trying to use a hybrid solution. Just for testing I've added an unlinked SSIS script task with the following code:
' Run hidden WinSCP process
Dim winscp As Process = New Process()
winscp.StartInfo.FileName = "C:\Program Files\WinSCP\winscp.com"
'winscp.StartInfo.Arguments = "/log=" + logname
winscp.StartInfo.UseShellExecute = False
winscp.StartInfo.RedirectStandardInput = True
winscp.StartInfo.RedirectStandardOutput = True
winscp.StartInfo.CreateNoWindow = True
Dim ServerHostFingerPrint As String = "ssh-rsa 6666 fa:fb:fd:e6:9d:4d:3c:4d:ca:cc:44:dc:34:68:73:46"
(NOTE: Using the  but the real username password and server are used in the task)
Dim strSiteAddressFirstPart As String = "[username]:[password]@[IPServer]"
Dim SessionAddress As String = strSiteAddressFirstPart + " -hostkey=\" + ServerHostFingerPrint
Dim RemotePathDirectory As String = "\Upload"
Dim FileNameToUpload As String = "C:\TEST OUTPUT\TEST.txt"
' Feed in the scripting commands
winscp.StandardInput.WriteLine("option batch abort")
winscp.StandardInput.WriteLine("option confirm off")
winscp.StandardInput.WriteLine("open " + SessionAddress)
winscp.StandardInput.WriteLine("put " & FileNameToUpload & " " & RemotePathDirectory)
' Collect all output (not used in this example)
Dim output As String = winscp.StandardOutput.ReadToEnd()
' Wait until WinSCP finishes
Is this code actually running the SFTP transfer??
I run the task and it runs from yellow to green but the file never transfers. What am i missing??
- Edited by LightStampCode Monday, March 07, 2011 9:53 PM edit
Wednesday, March 09, 2011 6:13 PM
Went back to this fine article which fit better into the ForEachLoop architecture and uses WinSCP.
written by Keith Hyer.
Works great after taking care of all the little gotchyas while plumbing it all together. Ended up using a named stored session of the WinSCP. the explicit breakdown, although more flexible, never worked correctly.
All works grreat now.
Thanks for the ideas.
- Marked As Answer by LightStampCode Wednesday, March 09, 2011 6:14 PM
Friday, April 01, 2011 6:13 PM
As a futher follow up. I ended up taking the SFTP completely out of the SSIS package. The pkg now graps data and writes out flat files only. An hour after it runs to produce the files into a directory, I set off and Robo-FTP script which does all the SFTP heavy lifting. Robo-FTP is super. It will even archive files sent and send an email to you if there's an error in the SFTP transfer.