I used Larry Franks extremely helpful post below to disassemble result
sets received from SQL adapter but I cant seem to access promoted
properties of the schema returned in my orchestration. i.e. I can't
access the column's values of the row returned. Can anyone tell me how
this can be done...if you could mention exactly at which point in the
procedure below I have to make modifications to access promoted
properties that would be great.

Also after processing all the records returned by SQL adapter in my
orchestration, I want to pass a value from the last record processed
by my orchestration to the SQL query that SQL adapter uses to return
records, for the next time it polls the SQLServer table for records.

P.S. Microsofts's documentation on Biztalk sucks 

BizTalk 2004 Disassembling Result Sets by Larry Franks:

Followup, here's the steps I've created for a kb article on this.
This is
based off the northwind database.
==================================
The following is a step by step guide on creating a receive port that
will
pull data from the employees table in the NorthWind database and split
the
inbound data into one document per record returned.

Create document schema:
1: Open Visual Studio and create a new BizTalk project named
NorthWindSplit
2: Right click on the project in Solution Explorer and select Add,
then Add
Generated Items.  In the Generated Item wizard highilght Add Adapter
and
click Open.
3: Select the SQL entry, make sure that we are pointing to the SQL
server
that contains the BizTalk message box database and that the message
box
database is selected.  Click next.
4: Click the Set button and enter the information to connect to the
SQL
server that houses the NorthWind database.  Once you have entered the
information click next.
5: For the target namespace enter http://NorthwindEmpSplit, leave the
port
set to receive and for the root element enter EmpRoot.  Click next.
6: On this page select to use a select statement and click next.  For
the
select statement enter:
select * from employees for xml auto
click next.  Then click finish.

You should have a SQLService.xsd file added to the project at this
point.

7: Open the sqlservice.xsd file and select the employees element.  In
properties for this set the max occurs and min occurs to 1.
8: Move the employees record to the root level (direct child of
<schema> )
and delete the EmpRoot element.
Save this schema.

Create the Envelope schema:
1: Add a new schema to the project, name it EmpEnvelope.xsd.
2: Open the new schema and highlight the <schema> element.  In the
properties window set the Envelope property to yes.  Set the target
namespace to NorthwindEmpSplit.  This must match the target namespace
for
the SQLService.xsd file.
3: Rename the root element to EmpRoot.  In properties for EmpRoot
select
the Body XPath property and click the ... button.  Select the EmpRoot
element.  This should set the Body XPath to /*[local-name()='EmpRoot'
and
namespace-uri()='http://NorthwindEmpSplit'
4: Right click the EmpRoot element and insert a schema node, Any
element.
This should create a child element named <Any>
5: Select the <Any> element and in properties set the Namespace to
##any.
Set the Process Contents property to Lax.

Save this schema.

Adding a custom pipeline:
1: Right click on the BizTalk project in Solution Explorer, add a new
item.
For the item select a receive pipeline and name it EmpSplitPipe.btp.
2: Open the pipeline file, from the toolbox drag an xml disassembler
to the
disassembler stage in the pipeline.
3: Select the disassembler you added, in the properties dialog set the
Document Schemas to the SQLService.xsd and set the Envelope Schemas to
EmpEnvelope.xsd.
Save this file.

Building and deploying:
1: Create a keyfile to sign the assembly and set this in properties
for the
project.
2: Build the project, then deploy.
3: Copy the dll for this assembly to the \program files\microsoft
biztalk
server 2004\pipeline components folder

Configuring BizTalk:
1: In Biztalk explorer create a new receive port, under this create a
new
receive location and set the transport type to SQL.
2: Select the URI entry and click the ... button.  Here set the
connection
string to point to the SQL server that contains the Northwind
database.
3: Select the SQL Command entry and click the ... button.  In the
Project
field select the assembly we deployed for our project and in the
Schema
field select the SQLService schema.
4: Change the Document Root Element Name to EmpRoot.  Ok out of the
SQL
Transport Properties.
5: Set the receive pipeline to use the pipeline we created in our
assembly.
6: create a new send port.  This should send to a file location.  For
filter settings filter on BTS.ReceivePortName==<name of receive port>
7: Start the send and receive locations.  The receive location should
start
polling the SQL adapter, submit the data to the pipeline which should
split
it into individual documents that are written out through the file
adapter.
==================================

Larry Franks

[ Post a follow-up to this message ]