DOWNLOAD THE CODE:
Download the Code 21896.zip

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.

End of Article


Post Your Comments Here

You must be a registered user or online subscriber to comment on this article. Please log on before posting a comment. Are you a new visitor? Register now

Reader Comments

I use a DROP TABLE `results` (Execute SQL Task) as the first step of the DTS package (where "results" is the name of the sheet that contains the data in the excel spreadsheet). Make sure you set it to the Excel file connection (in my case, connection 2).

David Wood- January 03, 2002

 
 

ADS BY GOOGLE