March 23, 2006 at 11:19 am
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
March 23, 2006 at 11:47 am
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
March 23, 2006 at 11:50 am
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.
March 23, 2006 at 1:42 pm
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
March 24, 2006 at 5:06 am
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