How to re-create Excel file from DTS.

[19511950]

Hi,

Everyday I need to create (or update) excel file (name.xls)
that will contain fresh data from sql server db.
When I try to run DTS second time it tells me that this file exist. How to delete existing file from the folder using same DTS?

Thanks,

Mark

 

[jhall01]

I like to use ActiveX scripts to call the FileSystemObject which will allow you to delete, rename, or move the existing file so that you can write a new excel document using the same name.

or you could use the xp_cmdshell procedure to delete the file if the name is not ever going to change:

Declare @delcommand varchar(300)

SET @delcommand = 'del '
SET @delcommand = @delcome + 'c:\yourdir\yoursub\yourfile.xls'

master..xp_cmdshell @delcommand

 

[19511950]

Can you be more specific, please, I've never use ActiveX scripts in packages.

How and where show I create this procedure?

 

[jhall01]

Insert an ActiveX Script task to the DTS then set the workflow "on completion" to flow from the ActiveX Script to the Source connection.

ActiveX example:

Dim, FSO, fileloc, newfileloc file

set fso = CreateObject("Scripting.FileSystemObject")
fileloc = "c:\folder\subfolder\file.xls"
newfileloc = "c:\folder\subfolder\subfolder2\file.xls"
set file = fso.GetFile(fileloc)	

'to delete
file.delete

'to move
'file.MoveFile newfileloc

set fso = Nothing
set file = Nothing

 

[jhall01]

the "Dim" line should read:

Dim FSO, fileloc, newfileloc, file

 

[19511950]

did I do something wrong? I place this code into Visual Basic ActiveX Script.
Error:Microsoft VBScript Compilation error
Error Description: Expected Identifier

-------------------------------------------------------
Dim, FSO, fileloc, file

set fso = CreateObject("Scripting.FileSystemObject")
fileloc = "\\Dev2\DDrive\test.txt"
set file = fso.GetFile(fileloc)

'to delete
file.delete

set fso = Nothing
set file = Nothing