help for Biztalk orchestraion

Question

Hello USers

i am stuckd in one place in my biztalk solution,

i have an orchestraion in which i am getting data from sql server (using generated schmema),and it its connected to decide shape,in which i have checked one of my database status column (by promoting property,as it is sql generate schmea i had to delete MAXOCCURENCE=UNBOUND), so now if status is approved i need to insert that row in another DB and if it is Pending than in another DB,now issue is if i take only one row in my source table it works fine,but if more than one row is there it creates problem,

plz put some light on this issue,if i am on the right way or not,i do not know loop shape,how to use it,i am assuming that my sql receive adapter will return me all the rows as in SP from which the schema is generated return all row,so i think in decide shape every row should be checked

thxs

Answer 1

One option is to use the concept of debatching so multiple instances (one per row in the table) of your orchestration gets activated. Your orchestration in turn will be only dealing with single row.

See this link for SQL and Debatching http://seroter.wordpress.com/2007/01/03/debatching-inbound-messages-from-biztalk-sql-adapter/

Answer 2

The SQL or stored proc is clearly capable of returning more than one row. If this is acceptable, then you have to understand how the SQL receive works. It can return one to many rows as the schema indicates. By altering the generated schema's cardality for the data rows, you break it.

You stated you promoted a propert to use it the decide shape. This is not necessary. You can use an xpath expression to extract a value from the message into a variable and then use it in the decide. The reason you had to modify the schema to not have many data rows is that you can only promote a value that appears once per message. Since the original schema was unbounded on how many rows it could have, it would not know which row to use for the promoted value.

The real issue here is that you want the data to come through a line at a time. You've got two options:

1. Do as Saravana Kumar mentions above.
2. Call a stored proc which will only return one row at a time.

Item #2 will require some tweaking on the receive port to ensure the Stored Proc is called repeatedly so long as data is being returned. If you don't do this, you'll be limited on the amount of polling cycles per minute.