DTS Job that retrieves a file through DOS???
-
The subject probably sounds crazy....but let me explain.
I need to retieve a file daily by means of an FTP that is done through DOS.
The following are the steps I would use through the Windows Run command:
1. type in the directory Z:
2. cd cms
3. then ftp 127.0.0.1
4. ftp
5. ftp [password]
6. ftp> hash
7. ftp> prompt
8. ftp> mget [name of file]
This will retrieve the file (which is a csv file) into the designated folder (cms)
I need to mimic the process through a daily DTS job on the SQL Server.
I hope that this makes sense...I am new to all of this. So it would be helpful if someone could tell me if this is possible, and then if it is, how I would go about accomplishing it.
Thank you!
-
make a cmd file to automate your ftp process. something like
z:
ftp -s:ftpfile 127.0.0.1
in the ftpfile, put login password etc, like
username
password
lcd cms
as
mget [name of file]
bye
In DTS, use Execute Process Task in task to include the cmd file. Then schedule the DTS package.
-
How about using the FTP task that comes with DTS, or even this handy replacement by the guys at SQLDTS??
http://www.sqldts.com/default.aspx?302
--------------------
Colt 45 - the original point and click interface
-
You can run a query using the xp_cmdshell extended procedure to execute DOS commands. You can type in your command statemetns and run in a DTS package.
Good Luck,
Mike
-
I began creating a txt file that has the steps for the http://ftp...but that is where I got lost...
I would like to use the suggestions from either Mike Howell or wz700...
I see the Execute Process Task within my DTS package...I just need some assistance with either creating the cmd file that can be executed from the package...
or the steps to use the xp_cmdshell within the DTS to execute DOS commands...
Would someone be able to assist me with this?
Thank you.
-
I would take the advice of Phill (no dis-resept to the other suggestions - but why re-invent the wheel). The site Phill recommends has plenty of samples to get you well on your way to a working solution.
Darrell
-
DSP,
Thank you for your response...I looked at the site...and I am more lost...it talks about writing a Visual basic script that will be executed by DTS..
I am not familiar with writing that Visual Basic script???
I understand that this would be much more efficient, but I would need assistance with writing the VB using the steps I provided in my initial thread..
Is this easy to write?
-
http://FTP.exe can take a script file as an argument.
Put your commands in this file.
Creat a scheduled job of type CmdExe that calls http://ftp.exe -s:MyScriptfile
FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-w:windowsize] [-A] [host]
-v Suppresses display of remote server responses.
-n Suppresses auto-login upon initial connection.
-i Turns off interactive prompting during multiple file
transfers.
-d Enables debugging.
-g Disables filename globbing (see GLOB command).
-s:filename Specifies a text file containing FTP commands; the
commands will automatically run after FTP starts.
-a Use any local interface when binding data connection.
-A login as anonymous.
-w:buffersize Overrides the default transfer buffer size of 4096.
host Specifies the host name or IP address of the remote
host to connect to.
Notes:
- mget and mput commands take y/n/q for yes/no/quit.
-
Have you tried using the FTP task in DTS? A what point doesn't it cater for your needs?
--------------------
Colt 45 - the original point and click interface
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply