Stored procedure output to Excel

  • Is there a way to take the output of a stored procedure using the data transform task, or do I have to execute it in a SQL task and create an intermediate table to make this work?

    The temp table is probably the answer.  It just seems klunky to have to create a table when the sp gives me a nice result set.

    So long, and thanks for all the fish,

    Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

  • Have you looked into using a FOR XML EXPLICIT stored proc to create the resultset in a XML stream.  With Active-X you could capture the stream and save it to a FSO.Textfile.  This would be a quick and filthy way to get the dataset in to an Excel readable format.

    -Mike Gercevich

  • No

    Create a source connection.

    An excel destination.

    Transform data task.

    Select sql query.

    Put in stored procedure name with parameter

    Select the destination tab, DTS will interrogate the proc for the resultset, and create a definition for you. You can arrange your transformations if you need to.

    and thats it.

     

  • I would have thought that would work.  It complains mightily that table #results doesn't exist.  So, it should work if I can get it to output the result set without using a temp table?  Of course, I'm using a temp table because of the complexity of the query.

    *sigh*

    Well, if this stuff were easy, anyone could do it.  Right?

    So long, and thanks for all the fish,

    Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

  • Is it any better with a table var??


    Thanks Jeet

Viewing 5 posts - 1 through 4 (of 4 total)

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