importing files off a ftp site to a dir

Hi: I am trying to accomplish grabbing files off the ftp site and downloading them into a dir. SInce neither the name nor the number of files in the ftp site dir are fixed, I tried something like below but with issues.
1. create a global variable that sets the connectionfile parameter to C:\ConnFOlder\connfile.txt
2. the connfile.txt looks like:
            ftp.x.com
            myusername
            mypassword
            mget *.*
            quit
3. An active script tries to set up a connection and download the files as follows:

Function Main()
 
Dim oConn, sFilename

Set sFilename= "local"
Set oConn = DTSGlobalVariables.Parent.Connections("connfile.txt")
oConn.DataSource = sFilename


Set oConn = Nothing
Main = DTSTaskExecResult_Success

End Function

But I keep getting the error theat object not defined (String:local). Am I missing something? Is there an easier way that I can involve the FTP object into the active script as well and solve the problem?


thanks.
LuckyLucksAsked:
Who is Participating?
 
nmcdermaidCommented:
try ftp -? at the command line. It lists all the switches.

-i

looks like the one you need.
0
 
nmcdermaidCommented:
You may have more luck using the window FTP client.

Firstly verify that the windows FTP has the functionality you need, by doing start/Run/CMD and experimenting with this syntax:

FTP << [your script file]

If you manage to get it to do what you want, then just drop the CMD syntax into an Execute Process Task in your DTS package.



If this is all you are doing in the DTS then you are probably better off doing it directly in a job
0
 
arbertCommented:
I agee, use the command stage in DTS and use the "DOS" ftp with the -s option and a command file.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mcmonapCommented:
Hi LuckyLucks,

Shouldn't the content of your ftp script file be more like:

myusername
mypassword
mget *.*
quit

The command line to run this would then be:
ftp -s:C:\ConnFOlder\connfile.txt ftp.x.com

This could be executed using the xp_cmdshell extended stored procedure:

DECLARE @ftpcmd VARCHAR(100)
SET @ftpcmd = 'ftp -s:C:\ConnFOlder\connfile.txt ftp.x.com'
exec xp_cmdshell @ftpcmd

However test this outside of SQL first to confirm the ftp bits works.  You can then add this as a SQL task to your DTS package if required.
0
 
LuckyLucksAuthor Commented:
w.r.t mcmonap's msg:
   Is there a modifiaction that I should be making to the command file s.t it retreives all files without expecting a yes. The reason is that I dont know how many files there might be beforehand.

Thanks
0
 
LuckyLucksAuthor Commented:
Also what is the -s for?

thanks
0
 
mcmonapCommented:
Hi LuckyLucks,

I think adding a /Y retrieves files without requiring entry, the -s specifies the script file to use.

myusername
mypassword
mget *.* /Y
quit
0
 
LuckyLucksAuthor Commented:
actually the /Y doent help. It does the first entry and then asks again for the second entry.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.