For the last 2 work days I’ve been battling with DTS and Foxpro files. I’m importing data from some DBF files into SQL Server, and noticed that if the DTS package failed, the DBF files were remaining locked and so could not be deleted from the working area – but only when executed through VB, through SQL Server directly it was fine.
The inability to delete the files contaminated my next import (assuming it happened within a minute or two of the failed import). Finally, after a lot of searching around and a serious amount of head scratching, Gary found a useful tip which said that the problem happened less if you specified a sql query (i.e. select * from tablename) rather than just specifying the table in the Table/View field of the “Transform Data Task properties” within the DTS Package. And it works. Kind of. It hasn’t eliminated the problem entirely, but VB can now delete the files. If I try and delete them manually I still get the “Cannot delete FILENAME: It is being used by another person or program.” error.
Useful resources were microsoft.public.sqlserver.dts, SQLDTS and SQL Server Central.
