Export DTS output into excel file

  • Dear all,

    I would to get advice from you all about this scenarion:

    I need to design a DTS package and output the file into excel format.

    However, it is not a direct data transform task, instead i use a stored

    procedure to retrieve the data.

    I am able to insert the sql query "exec sp......" as it prompts out

    the error that stating invalid object due to temp table in that

    stored procedure.

    Another way, I try to use "execute SQL task" in DTS, but how can i

    output the file to excel format?

    Please advice, thank you

  • Hi

    You could try the simple approach of creating a table that the stored procedure can use to store the data temporarily. Then get the stored procedure to insert into that table (truncating first of course to remove the data from previous runs) instead of producing a result set. You can then have the execute sql task to run the procedure, followed by the transform data task to export the table data to excel.

    There may be more elegant ways of doing this, but this would at least work.

    Regards,

    Duncan

  • Hi Golden,

    I don't have much experience with this particular T-SQL command . . .

    but I believe OPENROWSET may do the trick for you.

    There a couple of gotcha's with this command - do your homework.

    (The links below outline the requirements.)

    MSDN reference

    http://msdn.microsoft.com/en-us/library/aa276850(v=SQL.80).aspx)">(http://msdn.microsoft.com/en-us/library/aa276850(v=SQL.80).aspx)

    Topic discussion (not exactly this problem . . . but a good reference.)

    http://www.sqlservercentral.com/Forums/Topic436306-8-1.aspx

    Re-link of a good article/blog (as discussed above.)

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

    Hope this helps & good luck!

    M

    Free Expert Advice . . .
    http://xkcd.com/627/

    Mark

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

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