FTP Using DTS

Question

FTP Using DTS

santuvssantu

SSC Enthusiast

Points: 125
More actions
January 17, 2008 at 6:07 am

#70721

Hi Friends,
I need an urgent help on FTP Task in DTS. I want to connect to an FTP site and load all the files in a particular folder. This is a scheduled DTS so I can’t mention the file names on static basis. So I want to copy all the files to my local folder.
Please tell me the code to transfer the files using FTP to my local folder dynamically???
This is really urgent….so plz help me……..
Thanks in advance.

Viewing 6 posts - 1 through 6 (of 6 total)

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

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 1

There is a File Transfer Protocol task object you can use in a DTS package. I personally don't like it and create an Active X Script task that creates a script file containing FTP commands then execute it via cmdshell. Here's a code example. I store information like ftp site, username, password, and local directory in a database table and populate global variables in my DTS package with a Dynamic Properties task. If you don't want to do that, just replace the global variables with the actual values. Most important thing below is the command mget *. This is what will download all files in a directory from the FTP site.

Function Main()

Dim objShell

Dim strCommand

Dim strScriptFile

Dim strFolderPath

Dim objFSO

Dim objTS

Dim intRetVal

Const TemporaryFolder = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

strFolderPath = DTSGlobalVariables("FTPFolder").Value

' create the script file in the temp folder

strScriptFile = objFSO.GetSpecialFolder(TemporaryFolder).Path & "\FTPtask.scr"

Set objTS = objFSO.CreateTextFile(strScriptFile, True)

strCommand = "open " & DTSGlobalVariables("FTPSite").Value & vbCrLf

strCommand = strCommand & DTSGlobalVariables("FTPUser").Value & vbCrLf

strCommand = strCommand & DTSGlobalVariables("FTPPassword").Value & vbCrLf

strCommand = strCommand & "lcd " & strFolderPath & vbCrLf

strCommand = strCommand & "prompt off" & vbCrLf

strCommand = strCommand & "binary" & vbCrLf

strCommand = strCommand & "mget *" & vbCrLf

strCommand = strCommand & "disconnect" & vbCrLf

strCommand = strCommand & "quit" & vbCrLf

strCommand = strCommand & "exit" & vbCrLf

objTS.Write strCommand

objTS.Close

Set objTS = Nothing

Set objFSO = Nothing

Set objShell = CreateObject("WScript.Shell")

intRetVal = objShell.Run("ftp -s:" & strScriptFile, 1, True)

Set objShell = Nothing

If intRetVal = 0 Then

Main = DTSTaskExecResult_Success

Else

Main = DTSTaskExecResult_Failure

End If

End Function

santuvssantu SSC Enthusiast Points: 125 More actions · Answer 2

Hi Erik Kutzler,

Thanks for your reply.

If my FTP site is pointing to root directory and if I need to get the files of one particular folder, how can I do that here?

Thanks.

santuvssantu SSC Enthusiast Points: 125 More actions · Answer 3

Hi,

Got the usage of LCD and CD.

When I try to give a shared path like \\ this is not working. If i give a local path like C:\ all the files are copying properly. Can't we give any shared paths??

If our remote folder contains space eg: Property Template,

in ftp command if i give

lcd Property template --Enter

this is giving me error or path cannot be found.....why this is happening??

Thanks in advance.

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 4

Use the lcd command to specify where you want the files to be copied to on your network. UNC paths work.

Use the cd command to move to a different folder on the FTP site.

santuvssantu SSC Enthusiast Points: 125 More actions · Answer 5

Hi Erik,

Many Thanks for your help.I got it correctly.