Unanswered: Trouble with DTS package.. SQLServ->Excel
I've got a slight problem, and a few questions.
Problem: When making my DTS package dump from a SQL Server 2000 table to an Excel file, it keeps appending data. Unfortunately, deleting from the Excel file doesn't seem to work.. in a manual export, I tried the option to delete current table..and I got the error "Deleting data in a linked table is not supported by this ISAM." Creating a DTS package and adding a truncate task that does "delete * from my_table" generates the same error.
How do I wipe out the Excel file before dumping to it again?
Also, I need the ability to dump to a few different sheets within one Excel file, AND use formulas as well. Are there any helpful web sites that document how to do this using DTS?
I use a Dynamic Property Task to set the filename of the Excel file to whatever..based off a SQL query or something of that nature. On success, that calls a SQL task to create the table in the new Excel file.. and on success, then it copies the data from the SQL Server to the Excel file.
This seems to work great. Now I need to figure out how to dump data to different worksheets within the Excel file....
If anyone has a more efficient way of doing what I'm doing above, let me know. But it seems like it's OK.