DTS - deleting a text file after importing the data...
The DTS package that I am trying to get working has a couple of functions.
1) Source file = delimited text file
2) Destination file = SQL table
Basically, all I am trying to do is:
Add the text file to the SQL table, then upon successful completion, delete
the delimited text file.
In my approach, I created a batch file that simply has the DOS delete command
in it (Delete source.txt)
When I set the package up, I intended the completion of the text file import
to become a precedence for the execution of the batch file which will delete
the original text file (so that it won't be loaded a second time).
What is happening is that the tranformation works fine (i.e. the data loads
into the table properly). However, when the batch file is called, it creates
an error by indicating that it can't delete the source.txt file because it
is in use) I am suspecting that SQL isn't done with the file. The perplexing
part is that I thought that I set up the package to run the batch file based
on COMPLETION of the load.
Am I doing something wrong, or do I need to create a looping timer to give
enough time before running the delete source.txt command? The source.txt
file is generally only 5K or smaller in size! - The import portion of the
package really goes quickly, this is why I suspected that I may be doing
something wrong. I have to believe that the concept of importing data and
blowing away the source file is pretty common....
Thanks for any help.