Can SQL Server delete a file after importing data?

Question

Can SQL Server delete a file after importing data?

David-250683

Hall of Fame

Points: 3452
More actions
May 21, 2007 at 1:40 pm

#174355

Question: Can a regularly scheduled DTS import delete the text file which contained the data after the import is complete?
Background: Nightly, a non-SQL Server database exports data to a text file. Then, via a regularly scheduled DTS job, the data is imported into a SQL Server 2000 database.
When the non-SQL database exports data to the text file, the previous text file is written over so SQL Server is always pulling fresh data. However, on some occasions the non-SQL Server database does not have new data to export and thus never creates a new text file to write over the old text file. SQL Server though does not know the difference and performs it scheduled import - importing duplicate records.
One way of avoiding this is to have SQL Server delete the file after the import. Is that possible? Does anyone have a better suggestion?
The SQL Server table actually does not contain any constraints or primary keys. (I inherited this table and just working with it as is for now.) Would creating a primary key prevent this - would the primary key stop the DTS job from importing the duplicate data?
David

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 1

Yes, there are a few ways to do this.

One way would be to add an Active X script task after your import task that uses the FileSystemObject to delete the file.

Dim objFSO

Dim strFilePath

strFilePath = "c:\test.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(strFilePath) Then

objFSO.DeleteFile strFilePath

End If

Set objFSO = Nothing

Another way would be to add an Execute SQL task after your import task that executes xp_cmdshell to delete the file:

EXEC master.dbo.xp_cmdshell 'del c:\test.txt'

David-250683 Hall of Fame Points: 3452 More actions · Answer 2

David-250683

Hall of Fame

Points: 3452

May 21, 2007 at 4:35 pm

#707931

Thank you!!

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

it is usually better to move or rename the file with a datestamp so you have a copy for some period of time.

The answer to your second question is yes. A primary key or unique index will prevent duplicate data from being imported as long as the unique data is something being imported from the text file and not one that is being generated by SQL when the records are inserted.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Just my 2 cents...

Lot's of folks create a PK or Unique index with the IGNORE DUPES setting turned on. Whether that's true or not, I've found that having a key to start with slows down the import and it may cause logging unless the table has been truncated or is new to begin with.

I've also found that it's usually a bit quicker to import all the data into a staging table without checking for dupes and once the import is complete, do a dupe check and either a delete or mark the dupe rows so they can be filtered against...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David-250683 Hall of Fame Points: 3452 More actions · Answer 5

What would be the syntax for a rename instead of a delete?

Thanks for the input.

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 6

try

EXEC master.dbo.xp_cmdshell 'ren c:\test.txt c:\test.001'

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie