DTS Issue w/ FTP Batch File 2

[rukr]

I have an Execute Process Task in a DTS that executes a
batch file. The batch file is designed to pull a text file from an ftp site.
The batch file works when tested in Windows Explorer
and drops the ftp'd text file into the same folder with the batch file.
When I run my DTS and the Execute Process Task runs, it appears that the batch file executes, but the ftp'd text file is not where I expected it (in the same folder with the batch file). Can anyone please tell me what's going on here? Where is the text file going?

Thanks.

 

[mrdenny]

When SQL Server runs batch files, it starts them in the folder that the SQL server binary is in. EDit your batch files so that the first thing it does is change drives and directories to the correct folder for your files to be saved in.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[rukr]

I changed my batch file to begin with CHDIR
and pointed to the location on network I wanted the
file to be placed. It didn't do the trick. Is the
CHDIR command the appropriate one for this solution?

Thanks.

 

[gradley]

If your batch file has already connected to the FTP server at this point, try using the CD command instead of CHDIR.

 

[gradley]

The other method i've used is to designate the destination file and folder such as:

get file.txt F:\Files\Data\file.txt

 

[mrdenny]

If you are trying to save to a network drive you will need to use the full path to save to in the get command as gradley stated. You can't use a UNC path as the current directory.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Narizz28]

To set the local folder in ftp, use the command LCD C:\ (or whereever you want, without spaces in the path) in your FTP answer file. Or just use the FTP object in DTS to get the file.

 

[EdwinGene]

If you want to pull a text file into a particular directory you have to make that directory the default directory BEFORE you invoke FTP.

When you invoke a bat file using the Execute Process Task, the initial default directory is the SQL Server default directory, no matter where the bat file is located.

In the bat file you must invoke commands to change the default directory just as if you were doing it from a command window. Do this BEFORE you call ftp. Your bat file should have the following lines (E: is just an example drive. Your drive may vary):

E:
cd \dir1\dir2
ftp

This will pull the text file into directory E:\dir1\dir2