I built a Data Transformation Services (DTS) package that exports data from a SQL Server 7.0 table to a Microsoft Excel spreadsheet. The package's first run works fine, but subsequent runs just append the data to the spreadsheet instead of replacing the data in the spreadsheet. How can I avoid this problem?
I suspect that you want to execute a TRUNCATE statement, then create new inserts. You can accomplish these steps easily in two ways: Use Excel's automation model to empty the spreadsheet, or superimpose a blank copy of the Excel spreadsheet on top of the populated spreadsheet by using a small script such as the example that Listing 1 shows.