?
Solved

importing files off a ftp site to a dir

Posted on 2005-04-27
8
Medium Priority
?
221 Views
Last Modified: 2010-03-19
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.
0
Comment
Question by:LuckyLucks
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13881909
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
 
LVL 34

Expert Comment

by:arbert
ID: 13882647
I agee, use the command stage in DTS and use the "DOS" ftp with the -s option and a command file.
0
 
LVL 15

Assisted Solution

by:mcmonap
mcmonap earned 1600 total points
ID: 13883676
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:LuckyLucks
ID: 13885598
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
 

Author Comment

by:LuckyLucks
ID: 13885609
Also what is the -s for?

thanks
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 13886107
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
 

Author Comment

by:LuckyLucks
ID: 13887402
actually the /Y doent help. It does the first entry and then asks again for the second entry.
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 400 total points
ID: 13890717
try ftp -? at the command line. It lists all the switches.

-i

looks like the one you need.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question