DTS DataPump from a Stored Procedure

Hi,

assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?

The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )

At the SQL Query box, I have entered: exec my_proc

The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.

I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?Yes
Just invoque your stored procedure on your DTS. Then use the results as you want.

Paulo

Originally posted by andrewsc
Hi,

assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?

The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )

At the SQL Query box, I have entered: exec my_proc

The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.

I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?