Post reply

DTS and Excel

  • June 24, 2004 at 9:12 am

    #161741

    I have a DTS package that imports data into a Excel spreadsheet. I there are a total of five tables that are imported into 5 separate Excel sheets. What I want to be able to do is, when the package runs I ant to be able to delete the current data in the Excel spreadsheet in order to get the latest data back into it. Is there a way similar to truncating a table in SQL, to truncate or delete the data in excels? I would also want to maintain the Column headers in excel if this is possible. Or maybe there’s a way to recreate an excel document every time the package runs giving it the data and time plus file name concatenation?

     

    Any thoughts or suggestions would help.

  • June 25, 2004 at 2:13 am

    #511913

    Check out this article, it may help: "Deleting an Excel WorkSheet from within a package" http://www.sqldts.com/default.aspx?245

    A method I use is to have Excel templates set up in a directory and using ActiveX (Scripting.FileSystemObject) I copy the file to a reporting directory (giving it a new name based on data or date) and then pass that file name to a Transform Data Task to output the data to it.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply