How can I execute a stored procedure from a DTS package??

[statcan]

Hi

I am very new at this - I have been asked to call a stored procedure and convert its output into a text file that has been appropriately formatted. I know from reading your threads that it is possible using a DTS package - but I dont know how... I will need help at a level that assumes I am absolutely new at this...

Thanks in advance
Priti

 

[nowayout]

hi there,

when you try to import/export through the DTS it will show you three options that how do you want to tranfer 1) directly copy the tables and data 2) using Query to specify the data to transfer 3)Copy object and data between servers.

select the second option and then you can write the exec store procedure name and it will do that work.

Hope this helps.

take care

 

[statcan]

Hi nowayout,

Thanks for that pointer. I am sure it has got me to a good start. But when I created a package with a exec <proc-name> and tried to run it, it gave me error msg. Upon analysing the error, I am finding that the procedure is returning an empty rowset in the package while it is working perfectly in the query analyser. What am I doing wrong??

me

 

[lisali]

I got the same problem. I'm trying to export the ouput from a stored procedure into an Excel spreadsheet. Only the field names are inputed into the file, with empty rowset. Has anyone done this successfully?
Thanks.

 

[statcan]

Hi

I couldnt get the package to use the dataset created by the procedure. Looks like the only way I could get this to work is by creating a table in the stored procedure that holds the dataset and then making a &quot;Execute SQL Task&quot; run the procedure in the package and use a select statement on the table to create the text file...

Hope that helps you, lisali..