Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    65

    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?

    Thanks!

  2. #2
    Join Date
    Jan 2004
    Posts
    65
    OK I think I found a solution..

    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.

  3. #3
    Join Date
    Jan 2004
    Posts
    65
    bump

  4. #4
    Join Date
    Aug 2003
    Posts
    39
    Given you have a Connection to your DB called "Connection1" and a connection to the excel file called "Connection2".

    1. Add an "Execute SQL Task" Set the connection in the task to "Connection2".

    2. Then the SQL Statement would add the new sheet to the Workbook as follows:
    CREATE TABLE `tablename` (
    `field1` Long ,
    `field2` VarChar (1) ,
    `field3` DateTime
    )

    3. Next make an on completion workflow to "Connection1".

    4. Then a Transform Data Task to "Connection2".

    5. In the Transform Data Task the source tab would be the SQL Statement from which you are getting the data from the DB:
    SELECT [field1],[ field2],[ field3]
    FROM [DB].[dbo].[Table]

    6. The Destination Tab table name would be the sheet you want the data to goto. Make any Transformations you need to.

    Hope This Helps

    This is what the package should look like:
    Attached Thumbnails Attached Thumbnails exceldts.jpg  

  5. #5
    Join Date
    Jan 2004
    Posts
    65
    Thanks!! You've been a big help

  6. #6
    Join Date
    Aug 2003
    Posts
    39
    No Problem, let me know how it works out or if you found another solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •