Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    313

    Unanswered: Create a Package for export the query results to Excel

    Hi,

    In SQL Server,

    does anybody provide the steps for create a Package for exporting the query results to Excel environment?

    I just know that click the "Export", then choose the query file to export to Excel.

    I want to know how to create the Package to export it.

    Please let me know, thanks.

  2. #2
    Join Date
    Dec 2003
    Posts
    39

    Dts

    1. Create new package

    2. Connection from Connection menu (or right click)
    * Add SQL server connection, specify the server name, authentication, and dbname
    * Add Excel connection, specify the path where u want to place the excel file

    3. Transform data task from Task menu (or right click)
    * Add Transform data task, select the source and destination
    * Double click the task
    - Source tab : select the table/view or write down the query
    - Destination tab : just click ok on create table script window (for sheet & column name)
    - Transformation tab : Map the required columns

    4. Save the DTS

    5. Right click - Execute package or u can schedule it as job
    Link Link

  3. #3
    Join Date
    Sep 2005
    Posts
    313
    Question:

    I created the package and execute the package, it works,
    but, I got another problem, if I execute it for 2 times, the values will be output double in the same table,

    eg.
    1st execute:
    Name:
    Jim
    Mary

    2nd execute:
    Name:
    Jim
    Mary
    Jim
    Mary

    I want to execute the package which values will replace the previous file's values if the filename is same.

    How should I do? Please let me know, thanks.

  4. #4
    Join Date
    Dec 2003
    Posts
    39
    em.....i dunno how to delete data in excel destination file. but u can do it if the destination is ms access file, with additional task and workflow :
    - Add a task (Execute SQL Task) as 1st step in the DTS
    Existing connection : choose the ms access connection which was already created
    SQL statement : DELETE * FROM <destination_tablename>

    - Add on success workflow from above task to sql connection
    Last edited by Jelly Link; 12-30-05 at 02:28.
    Link Link

Posting Permissions

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