Jaa

Multiple ways to Poll (Receive Location in BizTalk) in the SQL Adapter

  • Artikkeli

In this post, I outlined the ways in which stored procedures can be executed using the SQL Adapter. Similar to the methods described there, are very similar methods for polling on the inbound side. (I won’t mention the advantages/limitations/workarounds here, since there are very similar to those mentioned in the other post).

1. The first method for polling, manifested as the “Polling” operation in the Metadata Search/Browse tool. At runtime, the SQL statement specified in the “PollingStatement” binding property gets executed, and the result sets returned (we support multiple result sets being returned) are surfaced as System.Data.DataSet[] (an array of datasets).

NOTE – in order to let the adapter know that you want it to perform this operation at runtime, set the InboundOperationType binding property to Polling.

2. The next approach was to try and expose the result sets as “strongly typed”. For this purpose, we added the “TypedPolling” operation which you can see in the Metadata UI. Metadata is obtained (at design time + runtime) using ADO.NET’s CommandBehavior::SchemaOnly option (which translates to SET FMTONLY ON).

NOTE – in order to generate the metadata, setting the InboundId property in the Connection Uri is mandatory – since in a single BizTalk application, you can have multiple receive locations with different PollingStatement values, and in order to differentiate the XSDs, the InboundId value is used in the XSD namespace (thus, the InboundId should be different for different Receive Locations)

NOTE – in order to let the adapter know that you want it to perform this operation at runtime, set the InboundOperationType binding property to TypedPolling.

3. In CTP4 (releasing end of October 2008), we’ve added one more mechanism. This was mainly done for backward compatibility.

In this method, the PollingStatement you specify should return data in XML format (e.g., SELECT Col1 FROM Table1 FOR XML AUTO). At runtime, the XML data returned is enclosed within the root tag defined by the values in the binding properties XmlStoredProcedureRootNodeName and XmlStoredProcedureRootNodeNamespace.

NOTE – in order to let the adapter know that you want it to perform this operation at runtime, set the InboundOperationType binding property to XmlPolling.

Comments

  • Anonymous
    October 10, 2008
    I am using the Siebel LOB adapter and have successfully used it to call from a BizTalk Send Port to send queries, inserts, updates and deletes to Siebel.How, I want to use the adapter from a BizTalk Receive Port/Location to "poll" Siebel for new data.  Is this possible?As a general comment it seems that "polling" is a feature of a specific adapter (SQL and SAP seem to support it).  However, what is the technical reason that this couldn't be supported for all adapters as a feature of the underlying LOB Adapter SDK?  Seems to me its just a "schedule" feature implemented with a timer layered on top of any adapter.  Perhaps its in there and I just haven't found it?Thanks,Rod
  • Anonymous
    October 10, 2008
    I am looking for an LDAP adapter to integrate with  Active Directory.  Is Microsoft planning one or do you know of one?Thanks,Rod
  • Anonymous
    October 18, 2008
    Even though polling is just a simple scheduling feature, the adapter needs to have the functionality of executing something on a schedule. The Siebel adapter does not have this functionality inbuilt.Regarding the LDAP adapter, you can try asking here: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1470&SiteID=1