sftp and ssis

Question

sftp and ssis

mejo1111

Old Hand

Points: 380
More actions
April 28, 2009 at 8:06 am

#133549

I need to pull a file daily from an sftp server. Any suggestions on how to do that?

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

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

Omoba SSCommitted Points: 1910 More actions · Answer 1

have you tried using an FTP task, there is an option of receiving files (amongst others) in the operation property, if categorized, it's under Misc.

------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]

mejo1111 Old Hand Points: 380 More actions · Answer 2

I don't think you can use the FTP task for SFTP files. I know there are 3rd party apps out there, I am just not allowed to use them.

Omoba SSCommitted Points: 1910 More actions · Answer 3

sorry, i can't help. just finding my feet in SSIS.

------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]

eharper SSC Eights! Points: 802 More actions · Answer 4

You might be able to do this with a script task.

However, having Googled a bit there is apparently no native support for SFTP within the .Net framework.

There are various third-party classes, some free, some not, but if you're not allowed to use third-party tools, that's no use to you.

The only other option would be to implement the SFTP protocol yourself from scratch (probably not simple).

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

jo.owens (4/28/2009)
I don't think you can use the FTP task for SFTP files. I know there are 3rd party apps out there, I am just not allowed to use them.

There is no out of the box support for SFTP in SSIS. You will need to purchase/build your own SFTP Task, or use a 3rd party command line product and the EXECUTE PROCESS task to accomplish what you need. By the way, we are sending files to a MSP using SFTP using the latter process.

mejo1111 Old Hand Points: 380 More actions · Answer 6

Thanks,

I downloaded winscp, it is not working, I am getting this error: In Executing "C:\Program Files\WinSCP\WinSCP.com" "-script=c:\myscript.txt" at "", The process exit code was "1" while the expected was "0".

I am assuming that I have the script wrong, correct?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I can't help you with winscp as I haven't used that particular product. You might try a search on SSC for winscp as i do remember seeing a few threads where it was discussed, just can't remember which ones.

Gator650 SSC Veteran Points: 205 More actions · Answer 8

I created a batch file that does all the SFTP information using Winscp.... from SSIS I just created a execute process task and point it it to the batch file and make sure the working directory is set to the batch files directory.

Near Kindest Regards, Gator

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 9

I use a client called SecureFx

http://www.vandyke.com/products/securefx/

I have it installed on a server. It has a command line feature that I use to invoke it via a SQL Agent Scheduled Job.

john.moreno Default port Points: 1485 More actions · Answer 10

jo.owens (4/28/2009)
I need to pull a file daily from an sftp server. Any suggestions on how to do that?

I hvaen't done so yet, but it's likely that I will have to do so soon. My first thought is to use a modification of THIS KB article that I did to allow SFTP. I plan on creating a script task, using that class (either as a referenced DLL or inlined in the script) and seeing how that works.

I don't want to use a third party application or component as it seems like overkill for the requirement...

John Moreno

NaffMonk Old Hand Points: 315 More actions · Answer 11

Hi Jo

I do this diaily using WinSCP using a script task to generate the file name which looks like this:

Public Sub Main()

Dim oFile As System.IO.File

Dim oWrite As System.IO.StreamWriter

Dim StrDate As String

Dim strDay As String = CStr(Day(Now))

Dim strMonth As String = CStr(Month(Now))

Dim strYear As String = Right(CStr(Year(Now)), 2)

If Len(strDay) = 1 Then

strDay = "0" + strDay

End If

If Len(strMonth) = 1 Then

strMonth = "0" + strMonth

End If

oWrite = oFile.CreateText("C:\FTP.txt")

oWrite.WriteLine("open ServerName")

oWrite.WriteLine("UserName")

oWrite.WriteLine("Password")

'oWrite.WriteLine("cd RequiredFolder")

oWrite.WriteLine("get ""RequiredFile.xls"" ""C:\RequiredDestination.xls""")

oWrite.WriteLine("Bye")

oWrite.Close()

Dts.TaskResult = Dts.Results.Success

End Sub

followed by an execute process task with arguments - "-s:c:\ftp.txt"

This works for me.

Gator650 SSC Veteran Points: 205 More actions · Answer 12

Naffmonk, I have been using a batchfile with winscp, but I think I will give your approach a shot now as a script task should in theory give me some more flexibility. Thanks for the idea!

Near Kindest Regards, Gator

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 13

Can you give me the method/s you used using winscp

Thanks

Regards

Kevin

sonyt65@yahoo.com

NaffMonk Old Hand Points: 315 More actions · Answer 14

WinSCP doesn't have an API, so there isn't any methods which are available to us. The usual approach would be to develop a winscp script which contains the desired commands, and then use an execute process task to run the script with WinSCP.

The above code sample is a simple way to generate dynamic scripts which is saved to a txt file every time the package is run.

If you want to do this programatically, you could try using the granados ssh library. I've never tried to use it, so if you do, reports back would be well received from me 🙂

Thanks