SQL Server Performance Forum – Threads Archive
DTS HelpI have a simple DTS package that runs a stored proc, exports data to an excel file, and them emails the excel file.
My only problem is the data in the excel spreadsheet is appended each time the package runs. Is there a way I can delete the existing rows in Excel before the new rows are imported?
For DTS question thishttp://www.sqldts.com is a very good source of information. I’m sure you’ll find an answer there. AFAIK you can always (re)create a file before new rows are inserted.
Frank, that’s for your help. I am a newbie with DTS, so after I found out you can run an Active X script in a DTS package, I just wrote a little code to delete the rows in excel first.
Glad, I could help [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br />So you’re more experienced in DTS. I nevery digged so deep that I wrote an ActiveX script.<br /><br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
Write an ActiveX task that deletes the old XLS file as the first task in the workflow.
Frank, DTS ActiveX scripts can work wonders. If you ever need to work with flat files in DTS research the FileSystemObject. So far ActiveX has met all my DTS file processing needs.
Raulie, I don’t have a need for DTS here. Be it fortune or not, I don’t know…
But I _know_ what you can do with the FileSystemObject. Lots of this nice and nasty stuff every (web)administrator is afraid of.
I found these posts when doing a search for same problem. I have a DTS package that inserts data into an Excel spreadsheet, but of course is just appending the data to data that is already there. Does anyone have any sample ActiveX code to remove data first? I haven’t tried doing any ActiveX scripting, but in the meantime I’ll see what I can come up with. Thanks in advance for any code fragments or tips! Patricia Norris
St Lucie County Health Dept
Hope this can help you out. http://www.microsoft.com/sql/techinfo/tips/development/truncatingdata.asp