Article Series on BizTalk and WCF: Part IX, BizTalk Adapter Pack BizTalk Patterns

UPDATE: I have since moved these articles to my own blog and they can be found here.

Whew.  My 9th and final article for on integrating BizTalk Server and WCF is now published.  This one looks at the BizTalk Adapter Pack and how to consume the Oracle adapter from inside BizTalk Server.  I demonstrated how to insert multiple records at once, call stored procedures, build a database polling solution, and how to call the Adapter Pack from an orchestration using code.

20,500+ words and 178 screenshots later, I definitely learned a lot about WCF (and Oracle) by writing this series of articles.  I found the security and attachment topics to be challenging, the transaction topics to be quite interesting, and the BizTalk Adapter Pack to be quite compelling.  The WCF integration in BizTalk Server 2006 R2, while not as rich as it could be, is actually pretty darn well built and thought out.

When I started out on this series, I figured that writing about BizTalk/WCF and then the BizTalk Adapter Pack would be somewhat “niche” for the majority of developers, but, hopefully this helps people down the line as they begin to really investigate and evaluate these technologies together.

Series Summary
 BizTalk and WCF: Part I, Operation Patterns Get the source code!
 BizTalk and WCF: Part II, Security Patterns
 BizTalk and WCF: Part III, Transaction Patterns
 BizTalk and WCF: Part IV, Attachment Patterns
 BizTalk and WCF: Part V, Publishing Operations Patterns Get the source code!
BizTalk and WCF: Part VI, Publishing Advanced Service Patterns
BizTalk and WCF: Part VII, About the BizTalk Adapter Pack Get the source code!
BizTalk and WCF: Part VIII, BizTalk Adapter Pack Service Model Patterns
BizTalk and WCF: Part IX, BizTalk Adapter Pack BizTalk Patterns


Technorati Tags: ,

Author: Richard Seroter

Richard Seroter is Director of Developer Relations and Outbound Product Management at Google Cloud. He’s also an instructor at Pluralsight, a frequent public speaker, the author of multiple books on software design and development, and a former editor plus former 12-time Microsoft MVP for cloud. As Director of Developer Relations and Outbound Product Management, Richard leads an organization of Google Cloud developer advocates, engineers, platform builders, and outbound product managers that help customers find success in their cloud journey. Richard maintains a regularly updated blog on topics of architecture and solution design and can be found on Twitter as @rseroter.

19 thoughts

  1. Richard, great series of articles!

    On your Order and Order Items insert scenario, how would you insert an order id generated by Oracle into the items table (like the order to order items scenario in SQL Server updategram where we use at-identity).
    Also, can you select multiple tables you want it to generate the metadata for or do you have to select one at at time?


    Thiago Almeida

  2. Hey Thiago,

    You mean that the “order id” column is a generated column? Haven’t tried that yet.

    As for multiple tables, YES, you can actually choose operations from many tables at once.

  3. Hi Richard,

    Yes, I was wondering how it would be possible to insert an order and its order items using only one call to the LOB adapter (in the same transaction) and having the auto generated column be the link between the two tables.
    I’ll have to start playing around with these adapters soon!

    Thanks, Thiago Almeida

  4. I’ve got a question about the completion of Scenario 3. I’m having trouble creating the enveloping, even after reading your posting about the SQL adapter.

    Any advice or examples you know of?

  5. Silly me, this is what happens when I’ve got 4 windows with your blog on them open…

    Anyhow, yes… I was asking about the oracle adapter post where you stopped short of making it “enterprise ready.”

    If you want to delete these comments, I’ll repost on the correct posting.

    1. I’ll take it as a compliment that you have 4 blog posts open at once 😉

      Yes, I see the text that you are referring to. Have you made the envelope schema yet? Did you take the original schema, mark it as an envelope, and then set the body xpath? Then you’d need to create a copy of that schema, rip out everything but the innermost POLLINGSTMTRECORD. Hopefully then, the envelope would debatch and you’d end up with that secondary schema type for each output message.

  6. Very similar to the solution I finally worked out:

    1. Set the generated schema to “Envelope=yes”
    2. Set the XPath.
    3. Set the root node to POLLINGSTMT (neccessary, or the message doesn’t route). I also set the Attribute FormDefault to Unqualified like you did in your SQL example.
    4. Set max occurs to 1 on POLLINGSTMTRECORD under POLLINGSTMT
    5. DELETE the POLLINGSTMT record (high level, up with the POLLINGSTMT) in the generated schema. This conflicts with the new schema created next.
    6. Create a new schema and “include” the generated schema. Note: You must copy the namespace of the original schema.
    7. Create a new record in the schame called “POLLINGSTMTRECORD”
    8. Set this new record as the root reference and it’s datatype to POLLINGSTMTRECORD.

    Now the xml pipeline will debatch successfully.

    I’ve had comments on forum posts that I should be careful setting the root reference, but it was the ONLY way I could get the error message that a schema for “POLLINGSTMTRECORD” was not found that aligned this solution with your SQL post.

    suggestions / Feedback?

    1. Hi,

      Please can you let me know which schemas I should set for
      Envelope and Documents schemas for xml dissambler.


  7. Richard,

    Can you please answer Thiago’s question on multiple tables inserts within the same transaction using WCF-SQL adapter?


  8. Durga,

    Haven’t tried it yet with taking the auto-generated ID returned from one table and making that a key in the second table while keeping it all under the same transaction.

  9. I searched so many applications but i did not find anything,luckily i got this links It is very useful to me.thanks to richards.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.