Q
I built a Data Transformation Services (DTS) package that exports data from a table in SQL Server 7.0 to a spreadsheet in Microsoft Excel. The package's first run works fine, but subsequent runs just append the data to the spreadsheet instead of replacing it in the spreadsheet. How can I avoid this problem?

A
It sounds like you need to execute a TRUNCATE statement, then create new inserts. You can accomplish these steps easily in two ways: by using the automation model in Excel to empty the spreadsheet, or by superimposing a blank copy of the Excel spreadsheet on top of the populated spreadsheet by using a small script such as the example in Listing 1.

LISTING 1: Sample Script That Superimposes a Blank Copy of an Excel Spreadsheet on Top of a Populated Spreadsheet

Dim sDBFileName
Dim sTemplateFile
Dim oFSO
sDBFileName = "d:\data\acc_demo.xls"
sTemplateFile = "d:\data\acc_temp.xls"
SET oFSO = CreateObject("Scripting.FileSystemObject")
'If the file exists, delete it.
IF(oFSO.FileExists( sDBFileName )) THEN
oFSO.DeleteFile( sDBFileName )
END IF
'Optionally, copy from another file.
oFSO.CopyFile sTemplateFile, sDBFileNam
eSET oFSO = Nothing