Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    8

    Question Unanswered: dynamic export to excel from web

    hi there,

    this is my first time really using DTS packages and am trying to export some data to an excel file through a jsp page. This isn't my main problem tho...

    the main problem is that i've coded the activeX script to dynamically name the file, as well as add the column headings in the excel file. The problem arises when I go to the data transform task and in the Destination tab, I can't select the worksheet that my code apparently creates.

    Naturally it won't run because it will return me an error saying that the destination doesn't exist.

    Any thoughts?

    Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Posts
    39
    Are you using SQL 2000? If so, the easiest way would be to use the dynamic properites task, and set the destination of the "transform data task" to a global variable which is your sheet name. Then each time you run the package, the desination is set the the sheet or table name you have created.

    Hope this helps.

  3. #3
    Join Date
    Nov 2003
    Posts
    8
    That's great help! Thanks!

    My next questions are:
    1. Is it possible to pop-up a Save As dialog box when you run the DTS? I tried the msoFileDialogSaveAs but of course it doesn't work because it's not an Office app.

    2. Is there a way to set global variables in jsp? I've seen code for asp, but haven't found any sample codes.

    Thanks again.



    Originally posted by SHICKS
    Are you using SQL 2000? If so, the easiest way would be to use the dynamic properites task, and set the destination of the "transform data task" to a global variable which is your sheet name. Then each time you run the package, the desination is set the the sheet or table name you have created.

    Hope this helps.

  4. #4
    Join Date
    Aug 2003
    Posts
    39
    I have a question for you. Does it have to be an excel file? Can it be a Comma Seperated File, which can be viewed in excel. If it can, I would not even use DTS, I would just create a stored proc, and execute it in java and then write the records to a a .csv comma seperated file. Then you can create a link to the file location for download.

    I don't know much about java, and how to interface DTS with it. I can only offer suggestions.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    I don't know if JSP supports it, but we use ADO to save a stream of data as XML. Then using an XSL-T transform, we set the file up just about any way the user wants it (comm-delimited, fixed width, different delimiters, etc).

    hmscott

    Originally posted by SHICKS
    I have a question for you. Does it have to be an excel file? Can it be a Comma Seperated File, which can be viewed in excel. If it can, I would not even use DTS, I would just create a stored proc, and execute it in java and then write the records to a a .csv comma seperated file. Then you can create a link to the file location for download.

    I don't know much about java, and how to interface DTS with it. I can only offer suggestions.

  6. #6
    Join Date
    Nov 2003
    Posts
    8
    Shicks: The problem with creating in a csv file is that if you have a large number field and you try to open the file in Excel, it won't retain the format of the number. I've had this happen on many occasion.

    Here's my current situation:
    I've now been able to create a dts package that exports data to an excel file. For security reasons, I saved the package as a .dts file on our server to be called by the webserver when run.

    The question is.. can I set the global variables of the package by referencing the file itself rather than the package on the server? Also, can this be done in java?

Posting Permissions

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