i work with SQL Server 2000. I have designed a DTS with a "Read File Transformation" (between 2 SQL Server 2000 connections). After this transformation is done, the original file has to be deleted. But the deletion of the file won't succeed because permission is denied. When i disable the step with the "Read File Transformation", the file is deleted without any problem. So this means that SQL Server has the authority to delete files on that specific location.
I already googled for this issue and i find the option "Close connection on completion" (Worklfow properties - Options), so i tried that one, but with no succes. But if i have 2 files that the "Read File Transformation" has to handle, i can delete the first file, but the second file gives again "Permission Denied"-error. So it seems that although the option to close connection on completion is active, SQL Server keeps the last transformed file open, so you can't delete it.
Any suggestions, hints or tips are heavily appreciated