Debatching Inbound Messages from BizTalk WCF SQL Adapter

Written by

A few years back now (sheesh, that long already??) I wrote a post about debatching messages from the classic BizTalk SQL adapter. Since that time, we’ve seen the release of the new and improved WCF-based SQL adapter. You can read about the new adapter in a sample chapter of my book posted on the Packt Publishing website. A blog reader recently asked me if I had ever demonstrated debatching via this new adapter, and to my surprise, I didn’t found anyone else documenting how to do this. So, I guess I will.

First off, I created a database table to hold “Donation” records. It holds donations given to a company, and I want those donations sent to downstream systems. Because I may get more than one donation during a WCF-SQL adapter polling interval, I need to split the collection of retrieved records into individual records.

After creating a new BizTalk 2009 project, I chose to Add New Item to my project. To trigger the WCF-SQL adapter wizard, you choose Consume Adapter Service here.

After choosing the sqlBinding as the adapter of choice, I chose to configure my URI. After setting a polling ID, server name and database name on the URI Properties tab, I switched to the Binding Properties tab and set the adapter to use Typed Polling.

Next, I set my PollingDataAvailableStatement to a statement that counts how many records match my polling query. Then I set the PollingStatement value to look for any records in my Donation table where the IsProcessed flag is false.

With my URI configured, I connected to the database, switched to the Service contract type (vs. Client), I’m able to choose the TypedPolling operation for my database.

When I complete the wizard, I end up with one new schema (and one binding file) added to my project. This project has a few root nodes which make up the tree of records from the database.

To make sure things work at this moment, I built and deployed this application. I added the wizard-generated binding file to my BizTalk application so that I’d get an automatically configured receive location that matches the WCF-SQL adapter settings from the wizard.

After creating a send port that grabs all records from this new receive location, I started the application. I put a new row into my database, and sure enough, I got one file emitted to disk.

That was easy. If I create TWO records in my database, then I still get a single message/file out of BizTalk.

So, we want to split this up so that these two records show up as two distinct messages/files. When using the old adapter, we had to do some magic by creating new schemas and importing references to the auto-generated ones. Fortunately for us, it’s even easier to debatch using the WCF-SQL adapter.

The reason that you had to create a new schema when leveraging the old adapter is that when you debatched the message, there was no schema matching the resulting record(s). With the WCF-SQL adapter, you’ll see that we actually have three root nodes as part of the generated schema. We can confirm this by looking at the Schemas section in the BizTalk Administration Console.

So, this means that we SHOULD be able to change the existing schema to support debatching, and HOPEFULLY it all just works. Let’s try that. I went back to my auto-generated schema, clicked the topmost Schema node, and changed its Envelope property to Yes.

Next, I clicked the TypedPolling node (which acts as the primary root that comes out of the adapter) and set the Body XPath value to the node ABOVE the eventual leaf node.

Finally, I selected the leaf node and set its Max Occurrence from Unbounded to 1. I rebuilt my project and then redeployed it to the BizTalk Server. Amazingly enough, when I added two records to my database, I ended up with two records out on disk.

Pretty simple, eh? When the record gets debatched automatically by BizTalk in the XML receive pipeline, the resulting TypedPollingResultSet0 message(which matches a message type known by BizTalk) gets put in the MessageBox and routed around.

Has anyone done this yet? Any experiences to share? This used the TypedPolling mechanism, but hopefully it’s not too different with other polling mechanisms.

Comments

77 responses to “Debatching Inbound Messages from BizTalk WCF SQL Adapter”

April 8, 2010

Debatching Inbound Messages From BizTalk SQL Adapter « Richard Seroter’s Architecture Musings

[…] [04/08/2010 Update: I've done a new post showing how to do this with the new WCF-SQL adapter. You can read that here.] […]

Reply
April 8, 2010

Zeeshan Shah

Nice writeup Richard .. Simple and transparent.

Reply
April 9, 2010

Patrick Wellink

Hi Richard, Excellent article, but i have one little question regarding the PolledDataAvailable statement.
What if you have several receive hosts running at the same time ? Do the Poll & get Data run within the same transaction ?

Cause when i was previously experimenting with the WCF-SQL adapter the adapter would complain if no data was found at the Poll statement. Now i don’t see this behaviour anymore. (maybe because of BT SP1). But i am still curious …

Reply
1. April 12, 2010
  
  Richard Seroter
  
  Hey there Patrick. Good question. To be honest, I’m not 100% sure. I would suspect that they are actually different. So that you could have adapters on multiple instances do the data check, but then only one will get the lock on the data itself. Would be good if they were together, but I haven’t explicitly tested that.
  
  Reply
2. February 25, 2016
  
  Jaydeep
  
  I think the Transaction part is handled by the “UseAmbientTransaction” option in the binding section. If it is set to “true” , I guess the polling is transactional.
  
  Reply
April 9, 2010

Scott Banwart's Blog » Blog Archive » Distributed Weekly 45

[…] Debatching Inbound Messages from BizTalk WCF SQL Adapter […]

Reply
April 9, 2010

Dan Rosanova

Great write up Richard! I have used this Typed Polling and was very happy with it. I had thought the debatching would work that way, but never did it and am really glad to hear about it. I will include this in my training course now because the old Adapter made this sort of a pain (even more so for people new to BizTalk).

As to the transaction question I was pretty sure these ran in the same transaction, but I will try to verify it this weekend.

I’d also point out some of the features related to transactions in this adapter. http://www.novaenterprisesystems.com/Blog/post/Transactions-in-the-WCF-SQL-Adapter.aspx

I think it fits well with the transactions question.

Reply
1. April 9, 2010
  
  Richard Seroter
  
  Good addition, Dan. I didn’t realize the ambient transaction would work that way (leaving things in source DB if no subscriptions in BizTalk bus).
  
  Reply
April 18, 2010

PS

Sir,

Any chance that you can suggest something on Oracle pollers. What happens if I need to debatch several message and then disassemble each one using a custom pipeline.

Any ideas are welcome.

thanks,
PS

Reply
1. April 21, 2010
  
  Richard Seroter
  
  I would hope that the process would be similar for Oracle or SQL adapter content. To your use case though, yes, pretty sure you’d need a custom pipeline to debatch twice. I swear years ago that I saw how to do a double-debatch without custom pipelines, but for the life of me, I can’t remember the details.
  
  Reply
  1. April 24, 2010
    
    PS
    
    Sir,
    
    Thanks for the reply. I ll try this and see. I am still struggling with the disassembling though.
    
    Reply
April 21, 2010

Patrick D

Thanks Richard. Great job always.
Little question about the WCF-SQL sample chapter in your book. I understand the map that constructs the BatchDistribution and BatchDistributionPickUp requests is just a sample in which DistributionID is hard coded. But how would we(you) do it in a real world scenario, where the “DistributionID” field in the BatchDistributionPickup table is the key(identity) that is generated after the BatchDistribution insert? Thanks
Patrick D

Reply
1. April 21, 2010
  
  Richard Seroter
  
  Hi Patrick. I’ve had others ask this sort of thing as well (extracting identity from an insert into table to use for foreign key in another) and I have yet to write that up. I don’t THINK this will happen by magic, but requires a bit of manipulation. I’ll move it up in the “todo” list 😉
  
  Reply
  1. April 22, 2010
    
    Patrick D
    
    No worries. I thought there was something already established for it. I’ll try to find a solution for it as well, and will definitely let you know. Thanks
    
    Reply
May 24, 2010

Adi Raina

Awesome!

I do have an issue that I am running into as I play with this. When the messages are debatched, the subscribing orchestration is getting a null message. I was trying to do direct binding to the message box.

Thoughts?

Thanks for the write-up!

Adi

Reply
July 1, 2010

Sunil Singhal

Hi,
Great exploration. No need to create a receive pipeline and specifying the envelope schema.

Reply
September 17, 2010

Sharif Atteia Issa

Great job Richard. For those wanting to debatch using the XmlPolling mechanism of the WCF-SQL adapter, it will not be as simple. You will probably end up resorting to the debatching method of the native SQL adapter meaning you’ll have to create a second schema for the body to ‘match’ the debatched record of the envelope.

I suspect this is because the wizard for the WCF-SQL adapter gives the options polling or TypedPolling (and not XmlPolling). I tried a few different methods to obtain a proper schema to receive the XML from the SP.

1) I executed the stored procedure using FOR XML AUTO, XMLSCHEMA. This produced a schema with multiple-root nodes. Tried to solve this by setting the root reference on the schema but this resulted in me not being able to set the maxOccurs property of the leaf node.

2) I ended up creating a WCF-SQL receive loc and executing the SP from there so I could use the received XML to generate the XSD and used this to generate a body message.

Reply
October 6, 2010

Brad

I followed the instructions exactly but I’m still getting a single XML output file. Can you help? Here is what I did:

1. Created schema via Consume Adapter Service. I’m used to using TypedPolling and know it’s working properly.

2. Deployed the project, created a File send port, filtered it to the receive port imported from the generated bindings file.

3. Ran the application and it does grab the data from my table and output a file to the send port location with all my records (in one file).

4. Then I changed the Envelope property of the ‘Schema’ node to Yes.

5. Changed the TypePolling Body XPath to the node above the leaf node. In my case, it’s ‘/*[local-name()=’TypedPolling’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/TypedPolling/neworders’]/*[local-name()=’TypedPollingResultSet0′ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/TypedPolling/neworders’]’

6. Changed the Max Occurence property of the leaf node (TypePollingResultSet0) to 1.

7. Re-deployed the project.

8. When I run the application again, I still get a single XML file with my records.

Any idea what I’m missing? Thanks!

Reply
1. February 1, 2012
  
  arizomo
  
  restart the host instance
  
  Reply
2. May 11, 2012
  
  Oleg Kleiman
  
  Be sure also you provided the same InboundID for both ‘Consume Adapter Service’ in Visual Studio and to the Properties of Receive Location that actually uses WCF-SQL Adapter
  
  Reply
October 15, 2010

Gilson

I’m new to Biztalk, is there a way of debatching a SelectResponse message created by the WCF SQL Adapter wizard when using the select method against a table?

Reply
1. October 28, 2010
  
  Richard Seroter
  
  Hi there,
  
  There should be. Research “envelope schemas” a bit, and notice that you can define an envelope for any schema, which should force it to get split up when going through a pipeline.
  
  Reply
October 31, 2010

Anthony

Thanks Richard, worked perfect; and you’re right, it is much simpler to configure de-batching…change here, tweak there and we’re golden.

Reply
November 26, 2010

manish

Hi richard,

I followed the same steps but unable to split the message.
Is the setting of X-Path is correct? or i missed sth

Reply
1. November 30, 2010
  
  Richard Seroter
  
  Your Xpath is pointing to the node ABOVE where you want to split it? What’s happening as your result?
  
  Reply
December 3, 2010

AUpadhyay

Hi Richard,

While reading from SQL store, what could be option to read the new updated row without using the flag status as additional column?

Reply
1. December 30, 2010
  
  Richard Seroter
  
  Another way could be to call a stored proc that compares the last update date to a last poll date that it persists elsewhere.
  
  Reply
December 30, 2010

2010 Year in Review « Richard Seroter's Architecture Musings

[…] Debatching Inbound Messages from BizTalk WCF SQL Adapters. I took a look at how to split messages coming in from the new WCF-based SQL Adapter. […]

Reply
February 3, 2011

Emil Åström

Great stuff indeed! I’ve got one question, though. Does this configuration work with promoted properties? I have selected an element similar to your DonorId for “quick promotion” but it doesn’t seem to get promoted as I can’t find it in the message context. Any ideas why?

BTW, I’ve read your book “SOA Patterns with BizTalk Server 2009” and really liked it. Well written, well informed and with great examples.

/Emil

Reply
1. February 4, 2011
  
  Richard Seroter
  
  Hi Emil,
  
  In which “type” did you promote the element? On the “TypedPollingResultSet0” definition (top node in the multi-part schema) or down where that type is included in other schemas? Can’t say for sure if that will matter, but it would make sense that it needs to be on the root definition.
  
  Reply
  1. February 5, 2011
    
    Emil Åström
    
    Both, with the same result 😦
    
    However, I solved my problem by mapping the top TypedPollingResultSet0 to a newly created intermediate schema containing the promotion (using the “inbound maps” setting of the receive port) and that worked rather well. Eventhough that’s an extra schema for the only purpose of promoting a value it actually made the message flow more easy to follow since the “TypedPollingResultSet0” name isn’t very descriptive.
    
    /Emil
    
    Reply
  2. February 15, 2011
    
    Traci C
    
    I just promoted a property in “TypedPollingResultSet0” and it worked! I see the data in the message context and my send ports are filtering on it.
    
    I must have a huge Biztalk learning curve ahead of me because I couldn’t get the debatching working correctly even though this article gives step by step. My problem was resolved by restarting the Biztalk service after deploying schema changes. Now that I’m doing that everything works like magic.
    
    Thank you everyone for all of the great info in this post. It’s been extremely valuable.
    
    Reply
    1. February 15, 2011
      
      Richard Seroter
      
      Glad it worked, Traci!
      
      Reply
February 6, 2011

Emil’s Blog » Property promotion when debatching messages from the BizTalk WCF SQL adapter

[…] Richard Seroter has a great post about how to debatch inbound messages from the WCF SQL adapter in BizTalk 2009/2010: https://seroter.wordpress.com/2010/04/08/debatching-inbound-messages-from-biztalk-wcf-sql-adapter/ […]

Reply
February 8, 2011

Cary

I followed the instructions exactly but I’m still getting a single XML output file.

I can obtain a single file containing many messages and I’ve followed your instructions for the “envelope and xpath” procedure

When I validate the schema I get

G:\Download\Projects\SimpleDBatch\SimpleDBatch\SP_CallTypedPolling.1.xsd: warning BEC1008: Node “TypedPollingResultSet0” – Body XPath property is invalid or references a node that may not exist in the schema.
G:\Download\Projects\SimpleDBatch\SimpleDBatch\SP_CallTypedPolling.1.xsd: warning BEC1008: Node “ArrayOfTypedPollingResultSet0” – Body XPath property is invalid or references a node that may not exist in the schema.

What am I doing wrong and what do you mean when you say “Next, I clicked the TypedPolling node (which acts as the primary root that comes out of the adapter)” ? What does acts as the primary root that comes out of the adapter mean?

Thanks Cary

Reply
1. February 8, 2011
  
  Richard Seroter
  
  Hi Cary, I think I was referring to the fact that when you have a schema with multiple root nodes, you can click the topmost node and set the “Root Reference” property.
  
  Reply
  1. February 9, 2011
    
    Cary
    
    Thanks Richard I thought as much. I’ve set the roof reference to TypedPolling and I’ve rebuilt and redeployed. I can see that the PollingDataAvailableStatement and PollingStatement are working but I get the following message via tracked event messages – message flow. Any ideas? Thanks
    
    There was a failure executing the receive pipeline: “Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35” Source: “XML disassembler” Receive Port: “WcfReceivePort_SqlAdapterBinding_TypedPolling__x0031_Custom” URI: “mssql://.//Test?InboundId=1” Reason: The type SimpleDBatch.SP_CallTypedPolling_1 is not a valid part type for a message. This invalid type may be from a property schema.
    
    Now I’m not getting any xml file.
    
    Reply
  2. July 5, 2011
    
    Jereme
    
    Cary, Did you ever Figure out why you are getting the error while De-batching? I am having the same issue and was wondering how you resolved it?
    
    Reply
    1. February 15, 2018
      
      Charlie Mott
      
      I also received the “not a valid part type for a message” error. (I was attempting to debatch a response message in a solicit-response send port using the WCF-SQL adapter).
      To get around the problem, I had take the generated schema and seperate it into seperate schemas for the request and send message.
      All working fine now.
      
      Reply
March 8, 2011

Sathish Kumar

Hi Richard

It’s greate article and de-batching couldn’t be any more simpler. However I am having little trouble in consuming all these debatched message inside the orchestration.

I have configured the schemas as envelopes and pipelines as per your writeup but when I execute this pipeline over the response port the I get all the debatched sql response into my orchestration. At this point my orchestration get’s suspended stating “Orchestration completed without consuming all of it messages”

when I checked the out folder orchestration was able to consume only one of the debatched message. It makes because the response port connect to only one receive shape and there is loop or co-relation set on this to consume all the debatched message.

Could you please help me or link that will guide me to make the orchestration consume all of it’s debatched message without getting suspended.

Cheers,
Sathish
But it keeps suspending

Reply
1. March 8, 2011
  
  Richard Seroter
  
  Sathish, do you need all the messages consumed by a single orchestration? If not, you don’t need a convoy and wouldn’t encounter this problem. Ideally, we are debatching so that we can process each message individually.
  
  Reply
  1. March 8, 2011
    
    Sathish Kumar
    
    Richard
    
    This is an POC I am working for a client, so basically I will have to come up with two options one with single orchestration and another with two orch’s by split the functionalites in and their cons & pros.
    
    Currently I am trying to consume all this message into same orchestration and make it work
    but later I also need to make it work with two different orchestration as well.
    
    This is what I did so far. In single orchestration I placed a two receive shape once takes a input trigger file and extracts the record from the DB then on the response port I dissemble the result set using the pipeline. In other receive shape of the I subscribed for the debatched response message type.
    
    On the one branch orchestration is able to consume all the debatched messages since I have intilized the co-relation and loop through every message but due to the other recieve shape my orchestartion is geting suspended.
    
    Reply
  2. March 10, 2011
    
    Sathish
    
    Richard
    
    Please let me know any articles or blogs where I could go through about convoys to achieve this.
    
    Reply
    1. March 10, 2011
      
      Richard Seroter
      
      Assuming you are debatching correctly (that is, you’re sure that you’re getting all the individual mesages out of the pipeline), then it does seem to be something off with your convoy. There is lots of information available about them online (see http://msdn.microsoft.com/en-us/library/ms942189(BTS.10).aspx or http://kentweare.blogspot.com/2008/01/processing-inter-dependent-files-using.html)
      
      Reply
May 31, 2011

David Syskowski

Hi, Richard. I greatly appreciate the expertise that you willingly share in your posts and your books. Through your guidance I can put together a typed polling application that consumes “flat” SQL result sets pretty quickly. I’m facing a situation now that has me wondering how to solve it, and so far I have not had success.

I have a header/detail pattern. A stored procedure returns the headers records in one result set (along with a column indicating the number of detail records), and the detail records in a second result set. The generated schema has a TypePolling node with two children (TypedPollingResultSet0 and TypePollingResultSet1). I can only set one body xpath for the TypedPollingNode and the result of that is that even though the incoming XML contains both result sets, only the one with the body xpath specified gets their messages posted to BizTalk. I have tried playing around with various settings and have even tried doing some structural modification to the schema but that usually ends up only breaking the application.

I was hoping to have a solution that was similar to your “BizTalk Message Aggregation For Email Pattern” since I do need to send out an email and that looks like a cool implementation.

Can I have both result sets debatched into BizTalk using typed polling, or should I be barking up a different tree?

Reply
1. June 30, 2011
  
  Richard Seroter
  
  Hey David. I can’t say that I’ve tried that. So your proc returns two total result sets and thus you get a schema representing each.set?
  
  Reply
  1. July 1, 2011
    
    David Syskowski
    
    Yes. I played around a good bit but couldn’t get it to work. I abandoned that in favor of having a debatched header record coming in, which I then use information from to make a call to return the set of detail records. Then I have used a more traditional loop to get each of the detail records. I used the xsd-generated class concept you presented to build a collection representing the entire object. The emitted XML from the entire process is just what I want.
    
    Reply
July 5, 2011

Ron Phillips

Works great, though it triggered throttling when I grab a full batch per pass (which in this case was about 50K-80K records). Then you get the seesaw: CPU maxes out for a couple of minutes, then throttling virtually stops the whole thing for a minute, then it kicks back up.

Instead of endlessly tuning throttling settings, my simpler solution was to have my SQL statement retrieve the top n records, with the actual number returned by a scalar function (since I had a dozen different polling statements, the function made it easy to tune the number of records retrieved at a time in one place). Cutting the number of records per retrieval down to about 10% of the total and having a polling interval of 30 seconds seemed to be about the optimum combination on this particular setup (two BizTalk boxes, one fairly beefy SQL box on a SAN).

Reply
July 13, 2011

Andrew

This is a simple and elegant solution but we have experienced some problems with debatched messages that seem to ‘go missing’.
We poll a sql db for available data and when records exist we execute a stored procedure to select the data then immediately update a processed flag as per your example above.
Intermittently (but often) the processed flag gets set by BizTalk but no debatched message is created in the MessageBox.
We’re using the WCF-Custom transport type with the sqlBinding type specified.
We’ve stripped the process right back so that this is running on one BizTalk server (2009) with no inbound map, but the problem still remains and is causing us a real headache.
We are now considering debatching using a loop within an orchestration instead.
Has anyone else experienced this problem?
If anyone has some advice please post a reply, I’m happy to provide further details.

Reply
1. July 13, 2011
  
  David Syskowski
  
  I have a thought, but want to hear first how you are handling the processed flag in your processing before offering it.
  
  What is the data type of your processed flag? And what are the steps contained within the stored procedure?
  
  Reply
  1. July 13, 2011
    
    Andrew
    
    Hi, that was a quick response!
    We update a ‘in process’ flag (varchar,1) to ‘Y’ and also add a datetime stamp to another field. The datetime stamp was a recent change to help us monitor the problem.
    
    Reply
  2. July 13, 2011
    
    Andrew
    
    And to answer the other part of your question…the SP first selects data from a view, then updates the in process flag and date stamp via the same view. Both the select and update use the same where clause and the whole thing is wrapped in a transaction.
    
    Reply
  3. July 13, 2011
    
    David Syskowski
    
    Andrew — for some reason the Reply link is not appearing under your two posts.
    
    Here is my thought and I’m not sure it applies but I’ll offer it anyway. We do something similar to you in several applications and so far we have had no problem. We deal with our processing flag in the following way (I’ll call it processed_flag for purposes of our discussion; ours typically are small ints):
    
    — The polling process looks for new records with the processed_flag with a value of zero.
    — When found, our stored procedure marks these records with a value of 1 to indicate that BizTalk now has them.
    — After the select statement that returns rows to BizTalk for debatching, we then mark all of the records marked as 1 to a value of 2, which indicates completed.
    
    The reason we are doing this is to prevent a record from being inserted into the table while BizTalk is grabbing the records and then being marked as processed without being touched.
    
    I’m not really sure if that might explain your problem or not but I know it is something we felt was a condition we wanted to protect against when we first starting working with this.
    
    Reply
July 13, 2011

Richard Seroter

Smart idea, David. Appreciate your contribution!

Reply
1. July 15, 2011
  
  Andrew
  
  HI again. David, your suggestions sounds like best practice. However we’ve experienced the issue with just one record in the view with none being inserted during polling.
  I have however implemented your suggestion but the problem persists =:-(
  
  Reply
  1. July 15, 2011
    
    David Syskowski
    
    Hmmm. I went back and reviewed your original post and the only difference I can see on the surface is that we use WCF-SQL as the adapter. In theory WCF-SQL and WCF-Custom with SQL binding should be the same.
    
    Is it possible to put a SQL trace on to see if there is anything there you can spot?
    
    Reply
  2. July 26, 2011
    
    Andrew
    
    Hi, an update for you. The trace showed nothing unusual, the pollingDataAvailable statement runs followed by the pollingStatement stored proc meaning records are set to being processed. However, not all messages are created in BizTalk.
    I changed the receive mechanism to get the entire typed polling sql message, then debatch using an orchestration but the problem persists – we still get data showing as in process but no message reaches BizTalk.
    I suspect this is to do with sql. The source db is sql 2005 on another domain in our organisation. I am not able to retrieve data at all when useAmbientTransaction is set to True. Any ideas welcome. Thanks..
    
    Reply
July 26, 2011

Ron Phillips

Andrew, I have seen similar behavior on my dev vm – it seems related to the number of records that are grabbed at a time. It was sporadic, I would be grabbing a few thousand records at a time, every 30 seconds, to get a total of perhaps 80,000, and out of those perhaps 50-75 would mysteriously never make it in to the message box.

My extremely crude solution was to not only set a status field but to also set a time stamp when my polling adapter picked up records. Once the records were processed by the orchestration they would get stamped with another status level. A schedule job runs to see if there are any records that have sat at the “picked up by polling” status for more than 10 minutes, which means they never hit the orchestration, and the status is flipped back to “waiting to be picked up by polling.

I suspect what is happening is one batch is picked up, and is still being submitted to the message box, and a new batch is picked up – even though it isn’t supposed to poll while publishing. Stretching out the polling interval or reducing the number of records per poll grab caused my processing time to exceed the time window available for the downstream system, so I had to resort to the crude fix I’ve described.

Reply
September 20, 2011

wask

Hi, Richard.

Through your guidance I can put together a typed polling application that consumes SQL result sets pretty quickly. I’m facing a situation now that has me wondering how to solve it, and so far I have not had success. A stored procedure returns the emp1 records in one result set, and the sales records in a second result set. The generated schema has a
TypePolling node with two children (TypedPollingResultSet0 and TypePollingResultSet1). I can only set one body xpath for the TypedPollingNode and the result of that is that even though the incoming XML contains both result sets, only the one with the body xpath specified gets their messages posted to BizTalk.

Can I have both result sets debatched into BizTalk using typed polling, or should I have to write pipeline component to detatched?

wask

Reply
December 14, 2011

Debatching Inbound messages from BizTalk WCF SQL Adapter | BizTalk Passion

[…] https://seroter.wordpress.com/2010/04/08/debatching-inbound-messages-from-biztalk-wcf-sql-adapter/ […]

Reply
April 2, 2012

Jesse Sarnovsky

Reblogged this on Jesse's Blog and commented:
Here is a great explanation of how to use polling with the WCF-SQL adapter.

Reply
May 11, 2012

It’s all about InboundID | Oleg Kleiman

[…] Adapter is complicated enough to dim this URL’s parameter. In the pioneering post of Richard Seroter it even does not mentioned. But it’s easy to provide different InboundID […]

Reply
July 4, 2012

Chris

Worked perfectly first time, thank you so much.

Reply
July 31, 2012

Marvin Perkins

Richard,

I was able to get your approach to work flawlessly using Typed Polling and the instructions you provided. Thanks! However, I need to do this with a Stored Procedure call which createds a solicit-response Send Port and also generates 2 schemas, one for the SP, and one for the ResultSet.

I have not been able to get my debatched output becuase no matter what I try in either or both schemas, BizTalk does not seem to like it.

Are there any particulars about using a Typed Stored Procedure that differ from using Typed Polling that I am missing?

Thanks,

Marvin

Reply
1. August 22, 2012
  
  Richard Seroter
  
  Hey Marvin, still experiencing this issue? I don’t recall the differences between the two, but likely someone on the BizTalk forums would know … http://social.msdn.microsoft.com/Forums/en-US/biztalkgeneral/threads
  
  Reply
  1. August 22, 2012
    
    perkinm@btol.com
    
    Richard, Thanks for the followup. I got everything working. I am using Typed Polling, but both my data available and data polling execute Stored Procedures. Also, I am getting and XML string as a response so I had to pick out the XML and then generate the schema(s) for it.
    
    I wasn’t expecting 2 schemas, but it worked out just fine because one was for the entire XML and the other was just for the portion I wanted to “debatch” as single messages. My Envelope and XPath Body settings were a little different than yours because of that, but once I got them figured out, it is working like a charm.
    
    My debatched XML are being fed in, one by one, to my Orchestration as single messages, no matter how many are included in the XML String.
    
    Thanks for setting me off in the right direction.
    
    Marvin
    
    Reply
August 18, 2012

Abhishek

Hi Richard

I am facing a issue .I have a insert operation on the table with WCF sql adapter .On stagging server table structure is [User1].Table name and on prod the table structure is [User2].Table name. Wat I found that schema structure itself is differing from stagging to Prod and thats reason SQL server is unable to validate the schema structure .

After that i tried to create Prod User with different credential on my local machine and deployed the new MSI with binding I have changed the SQL Credential according to prod SQL server .Than i found login failed . My SQL admin shown me that the user have all rights on the table.

Can you please let me know is there any issue which is causing this problem.

cheers

Abhishek

Reply
1. August 22, 2012
  
  Richard Seroter
  
  Hi Abishek, I have heard of that but not done troubleshooting myself. Try posting at the BizTalk forums http://social.msdn.microsoft.com/Forums/en-US/biztalkgeneral/threads !
  
  Reply
2. March 20, 2013
  
  boraxo
  
  I’m not Richard but I might be able to help. If the structure between stage and prod is different columns, then it will not work. But is is just created under a different user name, it’s easy to fix. Open the receive location, select configure.Go to Binding tab
  
  This will have your three SQL polling statements. Edit the table reference on all three. So change it from” FROM User1.Tablename to FROM User2.Tablename
  
  and it should work. Let me know if that does it. Good luck! – Ron
  
  Reply
  1. April 9, 2013
    
    abhishekcskumar
    
    Thanks boraxo. Its worked in my case .
    
    Reply
May 23, 2013

Chuck

Richard,
Love the article, and it works pretty well in BTS2010, only thing I had to do different was set the Inbound BizTalk message body to Path and then copy in the xpath to the cdata element of the incoming message. That is another story though.

My question is this: I can get typed polling to work flawlessly on the local sql instance. Once I create my db, table(s) and procs on my dev sql server.. I keep getting:

Microsoft.ServiceModel.Channels.Common.MetadataException: Retrieval of Operation Metadata has failed while building WSDL at ‘TypedPolling’ —> System.Data.SqlClient.SqlException: The statement or function must be executed in the context of a user transaction.

I have asked for help on this in the MSDN community.. and was hoping maybe you might have time to help me resolve this issue. Here is the link to the form post:
http://social.msdn.microsoft.com/Forums/en-US/biztalkr2adapters/thread/6db5b5b8-4412-4940-88de-89bae69b849a/?prof=required

Thanks,
Chuck

Reply
July 25, 2013

Rakesh

You saved my time. Thanks

Reply
November 18, 2013

Vishal

Hi,
Gr8 Post,
But I am facing 1 issue here.
I want to generate Flat File, on File location as the Output instead of xml message.
For this I created FF Send Pipline with Assembler component inside it.
When I deploy the Solution with Envelope schema Changes, and select the Send Pipeline –> ProcessFlow_ORION_FF_SendPipeline(the send pipleine that I have created), on the Send Port, I am getting below Errror,a dnunable to resolve it :-

Log Name: Application
Source: BizTalk Server
Date: 11/18/2013 11:44:44 PM
Event ID: 5720
Task Category: BizTalk Server
Level: Error
Keywords: Classic
User: N/A
Computer: WIN-SILSI848PAQ
Description:
There was a failure executing the send pipeline: “YASREF_ProcessFlow_ORION.ProcessFlow_ORION_FF_SendPipeline, YASREF_ProcessFlow_ORION, Version=1.0.0.0, Culture=neutral, PublicKeyToken=819ebb43c2f1f06e” Source: “Flat file assembler” Send Port: “SendPort_ProcessFlow_ORION” URI: “C:\YASREF\18 Nov 2013\Out\%MessageID%.txt” Reason: The document type “http://schemas.microsoft.com/Sql/2008/05/TypedPolling/sp_Orion#TypedPolling” does not match any of the given schemas.
Event Xml:

5720
2
1
0x80000000000000

14799
Application
WIN-SILSI848PAQ

YASREF_ProcessFlow_ORION.ProcessFlow_ORION_FF_SendPipeline, YASREF_ProcessFlow_ORION, Version=1.0.0.0, Culture=neutral, PublicKeyToken=819ebb43c2f1f06e
Flat file assembler
SendPort_ProcessFlow_ORION
C:\YASREF\18 Nov 2013\Out\%MessageID%.txt
The document type “http://schemas.microsoft.com/Sql/2008/05/TypedPolling/sp_Orion#TypedPolling” does not match any of the given schemas.

Thanks,
Vishal Sharma

Reply
November 18, 2013

David Syskowski

Have you verified the schema type that is being presented to the pipeline? The error message is stating that what was being expected is different than what is being received. So the first thing that I would check is the details of the transaction.

Reply
May 20, 2014

Debatching Inbound Messages from BizTalk WCF SQL Adapter | chieudong4712

[…] https://seroter.wordpress.com/2010/04/08/debatching-inbound-messages-from-biztalk-wcf-sql-adapter/ […]

Reply
July 13, 2016

Working with SQL in BizTalk and Mule – Tallan's Technology Blog

[…] SQL Polling with the WCF adapter is a very powerful method. My personal preference is for XmlPolling, which allows for greater control over the XML document produced from SQL (with some additional cost in creating the schema). That method is outlined by here. It’s also possible to have this kind of control and debatching using strongly typed polling – however, the node names and message structure may be more difficult to work with in complicated relational hierarchies (outlined in Richard Seroter here). […]

Reply
July 8, 2017

Prabhdeep Gill

Wow still works like charm in BizTalk Server 2013 R2 and BizTalk 2016 as well!
Thanks Richard!

Reply