Consume adapter not generating dataset from Stored proc

Question

I'm trying to call a Stored proc from BizTalk. I have seen many tutorials doing this. Only issue is, once I Consume Adapter -> connect to DB-> select a SP to run, the resulting schema doesn't have any the return values from the Stored proc (I assume this is what is meant to happen). Therefore I can't map the resulting schema to the one I want.

Please correct me if I wrong. This is my understanding:

1. Consume an Adapter, call a SP from the database in which there are some select statements.
2. This should result in a schema being generated which should have all the returned columns from the SP. (Do I have to add the output dataset which the SP will return to the schema manually? )
3. Map this to the desired schema
4. Output the result.

Answer 1

Number 2 is not correct;

2.This should result in a schema being generated which should have all the returned columns from the SP. (Do I have to add the output dataset which the SP will return to the schema manually? )

this happens only when you are defining output parameters in your SP, if your stored procedure is returning a record set from select statement the columns will be generated but in the run time, you can get the values with xpath.

in schema design you will only find a generated element of type any xml.