Debatching Inbound Messages From WCF SQL Adapter in BizTalk & Typed polling Debatching

Post by Richard Seroter

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.

Over coming  Problems with Above Approach

The problem is that when polling a database table for new data, we get a single message containing all matching rows. In most cases you probably want to debatch this message into separate message, one for each row, and it’s actually rather easy (see Richard’s post for the details).

There seems to be one problem though, namely that property promotions in the incoming schema (which is created automatically if you’re using typed polling) doesn’t work. One solution for that is to create a separate intermediate schema with the required promotions and transform the incoming messages into that schema using the “inbound maps” setting on the receive port. Note that the map’s source schema should be the top TypedPollingResultSet0 element of the generated schema, not the one below the TypedPolling element, since the transformation is done after debatching takes place.

As an added bonus you don’t mess up your message flows with “TypedPollingResultSet0″ message types, that are rather non-descriptive. A message type name of “RaindanceNotification” (or whatever you name the intermediate schema) is much more communicative.

Read more ...

Configuring SAP LOB Adapter Reciving / Sending Idocs from SAP to BizTalk

Receiving IDOCS from SAP

Before we do anything, there is an adapter gotcha we have to fix.  The SAP WCF Adapter needs an assembly which contains the SAP Property Schema for the idoc header. This is a standard assembly from Microsoft and you can find it in <sap wcf install path>\

Next step is to create a Receive Port and Receive Location ..

Change the bindings to this..

add user and password

verify the final URI (connection string)

Start the receive location and wait for the SAP Guys to send a idoc. Make sure you have tracking turned on and verify the receipt of the IDOC using HAT and also keep an eye on the application log for errors.  This took me a little while to get running as the receive location connects up to the Gateway and authenticates so its your first real test of the user account settings.

Troubleshooting Checklist

Make sure you have done all of these things …!

1. Got a valid user account to connect to SAP (the receive location will disable itself if this is incorrect)

2. Added the Biztalk Property Schema adapter resource to your BizTalk application (or reference another application with the resource)

3. Enabled access through any firewalls on ports 32<XX> to the SAP Server (uses standard client access ports)

4. Ensured the SAP team hae set up and registered a valid program ID on the SAP Gateway and pointed their RFC location to this. (the receive location will disable itself if this is incorrect)

5. Check your URI!

The minimum you need is this;

SAP//Client=    (leave blank for dynamic)

lang=EN (optional)

A/{ip address or name here}/00

ListenerGWHost={ip address or name}

ListenerGWServer={name usually sapgw00 but if multiple SAP instances then may be different}

ListenerProgramId={the registered program ID}

Hope this helps, for me this part took a while as we had many different instances to connect to and lots of firewalls to deal with.

There are Microsoft resources here also that have more details …

Read more ...

Generating a WCF Client for IDOC Operations Using the Add Adapter Service Reference Plug-in Using SAP Adapter in Biz Talk

This section provides instructions on how to browse, search, and retrieve metadata from SAP for IDOC operations using Add Adapter Service Reference Plug-in or Consume Adapter Service Add-in. Most of the instructions are same for both Add Adapter Service Reference Plug-in and Consume Adapter Service Add-in. Wherever applicable, separate procedures are provided for using Add Adapter Service Reference Plug-in and Consume Adapter Service Add-in.

Before performing the steps provided in the following sections, you must have:

• Created a Visual Studio project.
  
  
• Connected to the SAP system using Add Adapter Service Reference Plug-in or Consume Adapter Service Add-in. For instructions see Connecting to the SAP System in Visual Studio. Note that before connecting to an SAP system to generate schema or a WCF client for IDOCs, you must set certain binding properties.
  
  
  • GenerateFlatFileCompatibleIdocSchema
    
    
  • ReceiveIdocFormat
    
    
  • FlatFileSegmentIndicator
    
    
  These properties govern how metadata for IDOC is retrieved from an SAP system. For more information about these properties, see Working with BizTalk Adapter 3.0 for mySAP Business Suite Binding Properties. For instructions on how to set the binding properties see Specifying Binding Properties.
  
  

Browsing IDOCs in an SAP System

---

While browsing metadata using Add Adapter Service Reference Plug-in or Consume Adapter Service Add-in, the SAP adapter surfaces separate operations for sending and receiving IDOCs from an SAP system.

• Send and Receive. Adapter clients can use these operations to send and receive IDOCs from an SAP system using a strongly-typed schema. The adapter surfaces these operations separately for each IDOC and are available under the respective IDOC node in the Add Adapter Service Reference Plug-in or Consume Adapter Service Add-in.
  
  
• SendIdoc and ReceiveIdoc. Adapter clients can use these operations to send and receive IDOCs from an SAP system using a weakly-typed schema. The SAP adapter only surfaces one SendIdoc and ReceiveIdoc operation for all IDOC. These operations are available directly under the IDOC node in the Add Adapter Service Reference Plug-in or Consume Adapter Service Add-in.
  
  

Perform the following steps to browse IDOCs in an SAP system using the Add Adapter Service Reference Plug-in or Consume Adapter Service Add-in.

To browse IDOCs in an SAP system

---

1. In the Consume Adapter Service Add-in (or Add Adapter Service Reference Plug-in), from the Select contract type drop-down list, select the type of contract based on whether you will be performing inbound or outbound operations using the adapter.
   
2. In the Select a category box, click the IDOC node to see the IDOC message types in the Available categories and operations box. Alternatively, you can also see the IDOC message types by expanding the IDOC node.
   

   Tip
   You can directly go to the “immediate” category node or subcategory nodes in the tree, by typing the name of the artifact in while the focus is on the tree view in the Select a category box. For example, to jump to the ACC_BILLING IDOC message type, keep the focus on the IDOC node, and then type ACC_BILLING.

   The following figure shows the Consume Adapter Service Add-in listing the IDOC message types. The root IDOC node also surfaces a SendIdoc option to send weakly-typed IDOCs to the SAP system.
   
   
   
   

   Note
   For an inbound scenario, the root IDOC node surfaces a ReceiveIdoc operation to receive weakly typed IDOCs.

3. Click the IDOC message types to see the relevant IDOC type. The following figure shows the Consume Adapter Service Add-in with the IDOC type under a particular IDOC message type.
   
   
   
   
4. Click the IDOC types to see the different versions for an IDOC type. The following figure shows the Consume Adapter Service Add-in with the versions for a particular IDOC type.
   
   
   
   
5. Click the version of an IDOC type to see the supported operations on that IDOC type. The following figure shows the Consume Adapter Service Add-in with the operation supported for a particular IDOC type version.
   
   
   
   

Searching IDOCs in an SAP System

---

While searching metadata for IDOCs in an SAP system using Add Adapter Service Reference Plug-in or Consume Adapter Service Add-in, the SAP adapter:

• Supports wildcard characters in the search expression.
  
• Enables search immediately under the node at which the search operation is performed.
  

The following table lists the special characters that can be used for search and their interpretation by the SAP adapter.

Special character	Interpretation
+ (plus)	Matches exactly one character. For example, A+ matches AB, AC, AD
* (asterisk)	Matches zero or more characters. For example, A* matches A, AB, ABC.

For more information about the special characters supported by the adapter, see How Does the Adapter Surface SAP Metadata?
Perform the following steps to search IDOCs in an SAP system using the Add Adapter Service Reference Plug-in or Consume Adapter Service Add-in.

To search IDOCs in an SAP system

---

1. In the Consume Adapter Service Add-in (or Add Adapter Service Reference Plug-in), from the Select contract type drop-down list, select the type of contract based on whether you will be performing inbound or outbound operations using the adapter.
   
2. In the Select a category box, click the IDOC node.
   

   Important
   You can search for IDOCs only at the root level.

3. In the Search in category text box, enter a search expression to search for a specific IDOC message type. For example, to search for IDOCs that have "MATMAS" in their names, type *MATMAS* in the text box.
   
4. Click the button with the right-arrow icon to start the search. After the search is complete, the Available categories and operations box lists the IDOCs that satisfy the search criteria.
   The following figure shows the Consume Adapter Service Add-in listing the IDOC search result.
   
   
   
   

Generating Schema for IDOC Operations Using Consume Adapter Service Add-in

---

You can use the Consume Adapter Service Add-in to generate schema for selected SAP artifacts. Once you have browsed and searched for the artifacts you want to invoke, you can generate schema for those artifacts and send messages, conforming to the schema, to the SAP system.

Note
You can select category nodes to return all the operations in that category's sub-tree—for example, you can select an IDOC type (to generate schema for all the versions of IDOCs in that group) or select a specific version of IDOC to generate schema for only that version of IDOC. For more information about the nodes, see Metadata Node IDs.

To retrieve metadata for IDOCs

---

1. In the Consume Adapter Service Add-in from the Select contract type drop-down list, select the type of contract based on whether you will be performing inbound or outbound operations using the adapter.
   
2. In the Select a category box, click the IDOC message type or the IDOC type for which you want to generate metadata.
   
3. In the Available categories and operations box, select the IDOC type or the supported operations for which you want to generate metadata and click Add. The selected IDOC types or operations are listed in the Added categories and operations box.
   The following figure shows the Consume Adapter Service Add-in listing the selected IDOCs.
   
   
   
   
   If you want to generate schema for multiple operations, there may be some duplicate element definitions among these schema that may cause failure in compiling the BizTalk project. For example, consider a scenario where you generate schema for an operation “Op1”. The schema for “Op1” contains a parameter of complex data type “CT1”. After generating the schema for “Op1” you close the Consume Adapter Service Add-in and re-open it to generate schema for another operation “Op2”. Assume that “Op2” also contains a parameter of complex data type “CT1”. After you exit the Consume Adapter Service Add-in and compile the project, you will get compilation errors because the complex data type “CT1” is defined twice in different XSD files. In such situations, we recommend the following:
   
   • Generate schema for all the operations in a single run of Consume Adapter Service Add-in. This ensures that the Consume Adapter Service Add-in generates only one definition for the complex data type “CT1”.
     
   • If you want to generate schema for multiple operations across different runs of Consume Adapter Service Add-in, make sure you select the Generate unique schema types check box so that the generated XSD files contain unique namespaces for the complex data type “CT1”.
     
4. Click OK. The schema file is saved with an .xsd extension at the same location as the IDOC project.
   By default, the files are created with the naming convention "SAPBindingSchema<n>.xsd", where 'n' can be 1, 2, etc. depending on the number of schema files created. Alternatively, you can provide a custom name to the schema files by entering a name in the Filename prefix text box. The Consume Adapter Service Add-in now creates schema files with the naming convention <filename prefix>Schema<n>.xsd.
   

   Note

   The Consume Adapter Service Add-in also creates a binding file (an XML file) containing the binding properties that you specified while generating the schema for an operation and the SOAP action to invoke the operation. You can import this binding file in the BizTalk Server Administration console to create a WCF-Custom port with the connection URI, binding properties, and the SOAP action set. For more information, see Configuring a Physical Port Binding Using a Port Binding File.

5. On the File menu, click Save All.
   

---

You can use the Add Adapter Service Reference Plug-in to generate either WCF client code to send IDOCs to an SAP system or a WCF service contract to receive IDOCs from an SAP system.

To generate a WCF client or a WCF service contract for IDOCs

---

1. In the Add Adapter Service Reference Plug-in, from the Select contract type drop-down list, select the type of contract based on whether you will be performing inbound (receiving IDOCs) or outbound (sending IDOCs) operations.
   
2. In the Select a category box, expand the IDOC node, and then browse or search for the IDOC message type or the IDOC type that you want to send or receive.
   
3. In the Available categories and operations box, select the IDOC type or the supported operations for which you want to generate a WCF client (or WCF service contract), and then click Add. The selected operations are listed in the Added categories and operations box. You can select any node that is listed in the Available categories and operations box. If you select a category node, then all of the operations available under that node and its sub-nodes will be added.
   

   Important
   The Add Adapter Service Reference Plug-in generates a unique WCF client class (or WCF service contract) for each IDOC type. Depending on the categories and operations that you select, more than one WCF client class may be generated. For more information, see Generating a WCF Client or a WCF Service Contract for SAP Artifacts.

4. For most scenarios the default serialization options are sufficient; however, if needed, you can control several aspects about the code that is generated and the type of serializer that is used. To set these options:
   
   1. Click Advanced Options to open the Advanced Options box.
      
      
   2. In the Advanced Options box under Choose options for generated proxy, select the options that you want. For example, you can select whether asynchronous methods are generated for the WCF client or disable the generation of a configuration file.
      
      
   3. Under Serializer select the serializer that should be used.
      
      
   The following figure shows the Advanced Options box with the default selections (Auto is selected for the serializer and no other options are selected).
   
   
   
   
   The options that you can configure in the Advanced Options box are equivalent to some of the options available when you use the ServiceModel Metadata Utility Tool (svcutil.exe). For more information about these options, see "ServiceModel Metadata Utility Tool (Svcutil.exe)" at http://go.microsoft.com/fwlink/?LinkId=99821.
   
5. Click OK. The Add Adapter Service Reference Plug-in saves the WCF client class (or WCF service interface) and helper code for the operations and categories that you have selected in your project directory. By default, a configuration file is also saved. Slightly different files are generated for inbound and outbound operations; for more information, see Generating a WCF Client or a WCF Service Contract for SAP Artifacts.
   

Read more ...

Sending an IDOC to SAP with Max performance the Easy Way

Assume as part of your business process, you obtain an IDOC from somewhere, in a flat-file format, as below, which you wand to send to SAP using the SAP adapter with the least amount of effort ..
EDI_DC40 8000000000001064985620
E2EDK01005 800000000000106498500000100000001
E2EDK14 8000000000001064985000002000000020111000
E2EDK14 8000000000001064985000003000000020081000
E2EDK14 80000000000010649850000040000000200710
E2EDK14 80000000000010649850000050000000200600
The SAP Adapter exposes an operation called SendIdoc (while browsing, this operation appears under the Idoc node). The schema for this operation is such, that if you generated a WCF Proxy for it, the operation signature would be:
public void SendIdoc(string idocData, ref Guid guid);
Here, the "idocData" parameter would be set to the idoc data you want to send (i.e., the 6 lines above near the beginning of this post). And what about the guid? Well, IDOCs need to be sent to SAP as part of a TRFC client call. Every TRFC client call requires the client (in this case you+adapter) to generate a TID (which is a 24 character string), make a call to SAP using this TID, and then after the call, "confirm"-ing the TID. The adapter requires you to instead give it a GUID, which the adapter then internally converts to a 24 character TID. If you pass null for this parameter, the adapter will generate a GUID, and in the response message, give you the GUID it generated (that's why the parameter is "ref").
Now suppose you want to send this to SAP from BizTalk (of course, using the WCF-based SAPBinding + WCF Adapter combination). You therefore need to construct a SOAP message containing your 6-line idoc data. Luckily, the WCF Adapter eases some of the tasks involved in creating a SOAP message from raw data like the above.
Firstly, create a send port using the WCF-Custom adapter, and configure it for use with the SAPBinding. Now, for creating the SOAP message, you need to do two things:
(a) Specify an action - this can be done in the "General" tab of the dialog that pops up when you "Configure" the WCF-Custom port. Enter http://Microsoft.LobServices.Sap/2007/03/Idoc/SendIdoc. (this is the action for the Send Idoc operation)
(b) You also need to generate xml tags which wrap your data. If you look at the schema for the SendIdoc operation, you will realize that the body of the message has to look like:
<SendIdoc xmlns='http://Microsoft.LobServices.Sap/2007/03/Idoc/'><idocData>EDI_DC40........blah..blah</idocData></SendIdoc>
You could write a pipeline component or some custom code to create these enclosing tags. Or instead, you could take the help of the WCF Adapter. In the "Messages" tab of the dialog (that pops up when you "Configure" the WCF-Custom port), for the outbound WCF Message Body, select the "Template" radio button. And in the XML textbox/textarea, enter this:
<SendIdoc xmlns='http://Microsoft.LobServices.Sap/2007/03/Idoc/'><idocData><bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="String"/></idocData></SendIdoc>
The specified template puts the SendIdoc and idocData xml tags around the incoming data (treating it as a string) and sends it to the SAP Adapter.
If you're not interested in knowing the TID which was used to send the idoc, you should create a One-Way Send Port for maximum performance.
P.S. - if you're planning to use this procedure in your environment, you should contact BizTalk 2006 R2 support. There is a QFE you need for BizTalk 2006 R2 (related to the WCF Adapter) which you need for the above to work. The KB number is 942612.

Read more ...