Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66

    Unanswered: 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 ?

  2. #2
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163

    Re: DTS DataPump from a Stored Procedure

    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 ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •