DTS Delete - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Delete

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-08 : 09:56:01

I have a DTS that does the following:

1) Run a batch file that creates 2 dbIII dbfs
2) Reads applicable records from the dbIII dbfs into SQL tables
3) Updates a table with the records that were read into the SQL tables
4) Deletes the dbIII files

I'm not sure how to delete the dbIII files?? I thought a sproc would work, but the syntax is incorrect.

Any help and advice would be greatly appreciated!

SQL Server 7.0 SP3

Thanks!
Teresa

"Someday I'll know enough to help someone else!"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 10:01:41

You can create another batch file to delete the .DBF files and call it from DTS in the same way you call the batch file that creates them.

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-08 : 10:06:27

quote:

You can create another batch file to delete the .DBF files and call it from DTS in the same way you call the batch file that creates them.

Is there any way in SQL to do it or is this my only option?

Thanks!!!

"Someday I'll know enough to help someone else!"

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-08 : 10:09:18

I was just reading about scripts. Would one that deletes an fso work for me? I don't want to create a monster but would like to get away from the batch files.

Thanks again!

"Someday I'll know enough to help someone else!"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 10:12:26

Well, I assume that you wrote the batch file that creates the .DBF files, correct? When you say a batch file, you do mean a command-line executable .BAT file, right?

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-08 : 10:31:23

Someone else (a DOS programmer) wrote the batch file. It is a command line exe. As a company, we're trying to get away from the DOS type programming and convert everything to VB, SQL queries, etc. That's why I thought a script might work?? I think that eventually, the batch file that creates the DBFs will go away too.

However, having never worked with scripts in a DTS, I wasn't sure if I was going to open a can of worms.

"Someday I'll know enough to help someone else!"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 10:49:48

Open up NotePad and put in the following:

delete file1.dbf
delete file2.dbf

Save that as "delfile.bat". DO NOT CALL IT "delete.bat" or "del.bat" Change the names of the .DBF files to match yours. In your DTS job, add another Execute Process Task and put in "delfile.bat" as the Win32 process/batch file.

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-08 : 11:03:38

I just got off of the phone with my supervisor and he wants me to find a way to do it without a batch file.

If you have any other advice for me, let me know!

Thanks!

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-08 : 11:13:06

You should be able to do all of this with a DTS Package

If I were given this project, I would do the following:

1) Run a batch file that creates 2 dbIII dbfs
Execute Process Task (or an ActiveX Script task if you want to completely avoid using batch files.)

2) Reads applicable records from the dbIII dbfs into SQL tables
3) Updates a table with the records that were read into the SQL tables
Execute SQL Task (fire off a stored procedure that will read and update necessary tables)

4) Deletes the dbIII files
ActiveX Script Task (uses File System Object (Scripting.FileSystemObject) to delete files)

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-08 : 11:26:03

Thanks for the input about the scripts! Can you tell me where I can examples of scripts? I added the script object inside of a DTS, but I'm not sure how to code it. Do I create a separate function, etc?

Thanks again!

"Someday I'll know enough to help someone else!"

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-08 : 11:28:54

quote:

I added the script object inside of a DTS

please explain what you mean here.

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-08 : 11:44:14

quote:
please explain what you mean here.

I'm sorry I wasn't more clear. I'm not sure what I'm doing which makes asking the correct questions difficult.

Example:

I create a new DTS and drag the active script object to the window. Another window opens (activeX Script Task Properties) where I am to add code. I'm not sure of the ins and outs of writing the code in here. I need to find some fundamental info to help me learn.

Thanks!

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-08 : 11:46:00

There are a lot of good VB Script examples on http://msdn.microsoft.com/scripting You'll have to do some searching to find FileSystemObject examples. This simple VB script should get you started.

Function Main()
dim oFSO
dim filetodelete

set oFSO = CreateObject("scripting.FileSystemObject")

filetodelete = "f:myfile.dbf"
If oFSO.FileExists(filetodelete) Then
oFSO.DeleteFile(outputfile)
End If

set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function

Jeff Banschbach
Consultant, MCDBA

Edited by - efelito on 04/08/2002 14:05:01

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-08 : 11:48:58

Thank you, thank you, thank you!!! I wasn't sure if I could use the delete fso in here!!!

Thanks for the url too!!!

Teresa

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-08 : 11:56:57

I believe http://msdn.microsoft.com/scripting no longer works. I think http://www.microsoft.com/scripting still works but here is the link to the FileSystemObject

http://msdn.microsoft.com/library/en-us/vbenlr98/html/vaobjfilesystemobject.asp

efelito - very good code to delete the file.

Here is code to create the files for you.

Function Main()
dim oFSO
dim myFile

myFile = "F:\myfile.dbf"
set oFSO = CreateObject("scripting.FileSystemObject")
'if you dont care about overwriting Files
oFSO.CreateTextFile(myFile, true)

'if you do care then
If Not oFSO.FileExists(myFile) Then
oFSO.CreateTextFile(myFile)
End If

set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-08 : 12:30:46

TJ - You're welcome, glad I could help.

yakoo - Thanks! http://msdn.microsoft.com/scripting still works, it just forwards you to the scripting section of msdn. And I think http://www.microsoft.com/scripting forwards you to http://msdn.microsoft.com/scripting which forwards you to the scripting section of msdn. Go MS and the super forward link fest!

Jeff Banschbach
Consultant, MCDBA

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-08 : 12:36:43

efelito - my aplogies about the broken link. You were right. After further investigation, the link you posted above included the character '.' as part of the URL. I was taken to http://msdn.microsoft.com/scripting. instead of http://msdn.microsoft.com/scripting

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-08 : 14:06:36

Doh... no problem. I suppose I could have tested the actual posted link.

I fixed the post.

Jeff Banschbach
Consultant, MCDBA

Edited by - efelito on 04/08/2002 14:07:01

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources