SSIS Script task to FTP file

Question

SSIS Script task to FTP file

kwyman

SSC Veteran

Points: 210
More actions
March 11, 2014 at 12:08 pm

#305065

I am trying to write a script task (2008r2) to ftp a file from what I believe to be a unix server. The task executes successfully. However, no files are actually retrieved from the server. I know this because 1. I have done a complete search on the computer and 2. when the files are retrieved using Filezilla, they are tagged so that the file cannot be retrieved again. I have pasted my code below. If someone could help me, I would greatly appreciate it. Getting ready to go on vacation next week and would love to get this process automated.
Also, I have altered to include complete path and changed passive to false,
still no luck. Tx
Public Sub Main()
Try
'create the ftp connection
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'set the properties
cm.Properties("ServerName").SetValue(cm, "MyServer")
cm.Properties("ServerUserName").SetValue(cm, "MyUserName")
cm.Properties("ServerPassword").SetValue(cm, "MyPassword")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0")
cm.Properties("ChunkSize").SetValue(cm, "1000")
cm.Properties("Retries").SetValue(cm, "1")
cm.Properties("Passive").SetValue(cm, "true")
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
http://ftp.Connect()
Dim files(0) As String
files(0) = "FileName"
http://ftp.ReceiveFiles(files, "\\localPath", True, True)
http://ftp.Close()
Catch ex As Exception
Dts.TaskResult = ScriptResults.Failure
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
End Class

Viewing 5 posts - 1 through 4 (of 4 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Any reason you don't do this with the regular FTP task?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

kwyman SSC Veteran Points: 210 More actions · Answer 2

I tried that first and kept getting an error that the file could not be found. After searching for a solution on the error, I kept finding that there was a bug which did not allow ftp task work with a unix server.

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 3

We do it this way with no problems:

Have an ftp program sitting on the server somewhere (we use psftp)

Have a script task script out the FTP commands we want to use.

Use the Execute Process Task to run the ftp app along piping in the command file we just created.

Works like a charm every time.

kwyman SSC Veteran Points: 210 More actions · Answer 4

thanks for the response. I will keep that in mind. was thinking about purchasing a 3rd party tool to work with. Would like to alter my script as I did have this working to send files at the beginning so I know I should be able to receive. Just a matter of getting the script correct. thanks again~ Karma