Home > Biztalk, Biztalk 2010, WCF > Configure WCF-SQL Adapter for Inbound Operation with FOR XML Clause

Configure WCF-SQL Adapter for Inbound Operation with FOR XML Clause


Add Generated Item option in visual studio does NOT generate proper schema when using FOR XML EXPLICIT clause. This post shows how to configure a receive port to consume WCF service. Make sure that your application has been deployed before following the next steps

The following steps are to configure the WCF-SQL adapter to poll data:

  • Open Biztalk server administrator console
  • Expand your application
  • Select Receive ports node

  • Right click Receive Ports node
  • Select NewàOne-way Receive Port

  • Name the port
  • Click Receive Locations on the left panel
  • Click new to add a new Receive Location

  • Name the new receive location

  • Set Type dropdown to WCF-SQL
  • Set Receive pipeline to XMLReceive or any other custom pipeline
  • Click Configure button. You will get the following dialog

  • In the General tab, enter the SQL Server Address URI or click Configure. Clicking Configure shows the following dialog

  • Fill the following properties
    • Server name: for this walkthrough, I am using localhost because the SQL server is on the same machine as the dev box
    • InstanceName: leave blank if the instance name is the default instance
    • InitialCatalog: name of the database that contains the stored procedure
    • InboundId: use the stored procedure name. InboundId is used to make the URI unique. Biztalk requires the URI to be unique in an application. For more info read http://msdn.microsoft.com/en-us/library/dd787908(v=BTS.70).aspx
  • Click OK
  • Go to the Binding tab and fill the following properties

    • XmlStoredProcedureRootNodeName : The adapter will insert a root element to the received schema with this name and the name space of XmlStoredProcedureRootNodeNamespace. Make sure that the name matches the name in the xml document
    • XmlStoredProcedureRootNodeNamespace: The namespace for the RootNode in the previous step to be injected by the adapter. Namespace must match the namespace in the xml document
    • InboundOperationType: Because our stored procedure contains FOR XML EXPLICIT, we must use XmlPolling
    • PolledDataAvailableStatement: The sql statement is executed by adapter to check if data is available for polling
    • PollingIntervalInSeconds: How often, in seconds, do we want to poll data from the database. The lower the number, the more frequent the adapter hits the database and the busier Biztalk application will be in case the data is available
    • PollingStatement: the sql statement to be executed to poll data from the database
    • pollWhileDataFound: Set to false. For more info read http://msdn.microsoft.com/en-us/library/dd788416(v=BTS.70).aspx
    • UseAmbientTransaction: true to enable transactional behaviour
  • Configure the other tabs if needed
  • Click OK to finish configuring the receive location
  • Click OK to finish configuring the receive port
  • Now your port is ready for usage
Advertisements
Categories: Biztalk, Biztalk 2010, WCF Tags:
  1. Jessica
    July 26, 2011 at 10:57 am

    Can i please see a copy of the xml that is created by the proc

  2. Lajak Technologies Inc
    July 26, 2011 at 12:52 pm

    The values of XmlStoredProcedureRootNodeName and XmlStoredProcedureRootNodeNamespace are injected by the adaptor into the xml document.

    You can read more about it at
    http://msdn.microsoft.com/en-us/library/dd787981%28v=bts.10%29.aspx
    http://msdn.microsoft.com/en-us/library/dd787898%28v=bts.10%29.aspx

  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: