Deleting System Text File

[EdwinGene]

I am using DTS to FTP a file to a remote server. As part of this process I create an FTP commands file for FTP.exe to use. The last task in my DTS process is supposed to delete this FTP commands from from the hard drive. I use an ExecuteSQL task to do this with the following command:

Execute master.dbo.xp_cmdshell 'del E:\RPProc\RcpsOut\DDFTPCommands.txt'

If I execute the entire DTS package with the Execute button the ExecuteSQL task is executed and the DTS package successfuly completes, but the FTP commands file is not deleted from the hard drive. HOWEVER... If I manually execute the ExecuteSQL task by right-clicking and selecting 'Execute Step', the FTP commands file is deleted.

Obviously I can't have my customer manually delete the FTP commands file whenever this DTS package is run, but I can't figure out why the FTP commands file is not being deleted automatically.

Does anyone know what is happening, here?

 

[nigelrivett]

I do ftp via an SP.
see

http://www.nigelrivett.net/s_ftp_PutFile.html

And an xp_cmdshell to delete the file at the end works fine.

I suspect your problem is either trying to delete the wrong file or ftp still having the file open.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[EdwinGene]

Not to say that you're wrong, because I can't think of how I would check if FTP.exe still has the commands file open, but...

1. FTP.exe successfully accesses and uses the commands file, and the path and filename of the commands file sent to FTP.exe as a parameter is exactly the same as the commands path and filename in the command line sent to xp_cmdshell to delete the commands file.

2. If I was trying to delete the wrong file, why would it work when I execute the step with the delete, manually?

3. The last command in the commands file is 'quit', and like I said FTP.exe successfully accesses and uses the commands file, so I can't see how FTP would still be open.

 

[EdwinGene]

The Message Text can also be set to the contents of an .INI or data file, or the results of a SQL query.

 

[EdwinGene]

oops! Sorry, I though I was replying to another thread. Kindly disregard my previous post.

 

[nigelrivett]

2. A problem with the workflow?
3. A problem with the workflow? Also ftp has a habit of looking as if it's terminated before the task is complete. It used to be a problem that ftp would complete when it started to flush the last buffer so if you accessed a small file straight away you could miss the last bit of data. Haven't seen this for a while but it used to be a common problem. You may be seeing something similar. What happens if you put in a delay before the delete? That will at least show what the problem is if it works.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rasETL]

EdwinGene

Sometimes you just can't figure out why it will not work one way, but then work another way.

Two alternatives to try...

1. Leave the ftp command file out there at the end of the DTS package, and revise yoour package to delete the file as the first task in the process. This may solve the problem , but also allows you the ability to see what was executed, which could help if problems arise.

2. Make the detlete task a second DTS package, and run the two packages within a "main" package. Execute the delete task pacjakge with an "on success" condition.

Hope that helps.