Deleting a text file - DTS (Data Transformation Services)
I have a problem that I can't seem to solve with a very simple DTS Package.
There are three steps:
1) [Point to the source file] The first object is "Connection 1" which is
a text file
2) [Move the data to an SQL table] The second connection is an SQL table
(the two objects are conected with a solid black (transformation) arrow.
3) [Delete the source text file] The third step (on success) is an "Excute
Process" task. This is configured as a batch file (kill_txt.bat). The batch
file has one line in it that simply uses the DOS delete command to get rid
of the file that I just loaded. "delete position.txt".
The problem is that step 3 fails because step 1 or 2 must still have the
source file open. To me this is very difficult to understand because a 1K
file should load quickly and if the process passes (on success), then why
would the source file still be tied up? After closing the package, I have
no problem deleting the file through Explorer or a DOS command window.
To me, this problem seems too simple - It is an extremely common practice
to load data from text, then delete the source file. Therefore, I must be
missing a basic point. I am trying to stay away from creating a delete job
which is called upon completion of the first load job. I would really appreciate