Composite Operations on BizTalk WCF- SQL Adapter - Calling Multiple Store Procedures

The new SQL Adapter in the WCF Adapter Pack 2.0 supports composite operations, that is performing multiple SQL operations in response to a single input message. The purpose of this blog post is to provide a walkthrough of how this works. I am using the public beta of BizTalk Server 2009, the public beta of the WCF Adapter Pack 2.0 and released version of SQL Server 2008. See below for an important caveat, as well as a link at the end of the post to my test solution.

To start, as with most things in BizTalk’s contract-first world, we need a schema. In order to do this, choose “add generated items” from a BizTalk project in Visual Studio. Then, choose “Consume Adapter Service”. If you don’t see that Visual Studio template, then you haven’t installed the Adapter Pack, as that’s where it comes from.

Next, select the sqlBinding specify a server, and press “Configure”. Set the client credential type to Windows (assume appropriate SQL login rights), and then on the URI tab, specified the server and database to use:

Af6ter doing this, press “Connect”, and the metadata will be populated.

For the purpose of this walkthrough, I have created 2 stored procedures: the first one inserts a record into a table, the second returns all rows in that table. Those are shown in the UI below.

Note that in the category we have “Procedures” and “Strongly-Typed Procedures”. The distinction is that “Procedures” will create un-typed schemas, whereas “Strongly-Typed Procedures” will generate schemas that you can work with inside BizTalk for mapping, promoting properties, etc.

The “Filename Prefix” will be used as a prefix for all the generated schemas.

After that was configured, I clicked OK and all the schemas were generated for me.

Next step is that you need to create a composite schema that will define the message you send to the adapter. I’m not quite sure why this one wasn’t generated for me, it’d be nice (hint hint), but it’s trivial to do.

How I did this for the walkthrough:

• create a new schema
• rename the root to SQLMsg (or whatever you like, this is unimportant)
• add a sibling record called SQLMsgResponse (this name does matter, it is the name of the request, with “Response” appended)
• add two child records under SQLMsg, and another two under SQLMsgResponse (names don’t matter, they’ll get renamed below)
• right-click the topmost “<schema>” node, and in the “Imports” property, add the “CompositeTypedProcedure.dbo.xsd” schema
• in the first child under SQLMsg, set the “Data Structure Type” property to InsertIntoDestination (this is a reference that you just imported above)
• in the second child under SQLMsg, set the “Data Structure Type” property to SelectAllDestination
• in the first child under SQLMsgResponse, set the “Data Structure Type” property to InsertIntoDestination (this is a reference that you just imported above)
• in the second child under SQLMsgResponse, set the “Data Structure Type” property to SelectAllDestinationResponse

Your schema should now look like this:

 

The, create an instance of the new composite schema to use as a test message, and populate the request. Here’s mine:

I then created a simple orchestration that would receive a request, call the adapter, and persist the response from the adapter. The request and response messages are of the type we just created in the composite schema:

Build and deploy the solution. After deploying it, note that there was a binding file generated along with the schemas, which is awesome, as this means you don’t need to manually create the send port. So, import the binding file which exists in you Visual Studio project.

HOWEVER… pretty big caveat here…. after importing the binding, you need to change the action mapping. If you use the default value, it will fail. You need to replace what is generated with the magic keyword “CompositeOperation”. This tells the adapter that it needs to call multiple operations, which it will resolve based on the schemas and namespaces. I believe the reason this works the way it does is that it allows you to import multiple operations in a single pass, and then use some subset of those operations in a composite operation, thereby enabling re-use of the generated schemas to potentially cover multiple different combinations of composite operations. Either way, watch out for this one. The error message tells you exactly what the problem is, however it won’t tell you about the keyword.

As an aside, and for the benefit of those who have not worked with this adapter yet, here are the binding configuration properties you have access to:

Then:

• create an inbound file drop location
• create an outbound file drop folder
• bind everything
• start the application
• drop your instance doc into the file drop location, triggering the orchestration

Lastly, here’s the output file:

In closing, I think this is an awesome new capability, and I am really liking the new SQL adapter. In case you haven’t heard, the old SQL adapter is being deprecated, so you really should be working with this one going forward.

Read more ...

Implementing FIFO Pattern in Biz Talk 2010

1. Problem You are implementing an integration point where message order must be maintained. Messages must be delivered from your source system to your destination system in first-in/first-out (FIFO) sequence. 2. Solution In scenarios where FIFO-ordered delivery is required, sequential convoys handle the race condition that occurs as BizTalk attempts to process subscriptions for messages received at the same time. Ordered message delivery is a common requirement that necessitates the use of sequential convoys. For example, FIFO processing of messages is usually required for financial transactions. It is easy to see why ordered delivery is required when looking at a simple example of a deposit and withdrawal from a bank account. If a customer has $0.00 in her account, makes a deposit of $10.00, and then makes a withdrawal of $5.00, it is important that these transactions are committed in the correct order. If the withdrawal transaction occurs first, the customer will likely be informed that she has insufficient funds, even though she has just made her deposit. Sequential convoys are implemented by message correlation and ordered delivery flags in BizTalk Server, as outlined in the following steps. Open the project that contains the schema. (We assume that an XSD schema used to define a financial transaction message is already created.) Add a new orchestration to the project, and give it a descriptive name. In our example, the orchestration is named SequentialConvoyOrchestration. Create a new message, and specify the name and type. In our example, we create a message named FinancialTransactionMessage, which is defined by the FinancialTransactionSchema schema. In the Orchestration View window, expand the Types node of the tree view so that the Correlation Types folder is visible. Right-click the Correlation Types folder, and select New Correlation Type, which creates a correlation type and launches the Correlation Properties dialog box. In the Correlation Properties dialog box, select the properties that the convoy's correlation set will be based on. In our example, we select the BTS.ReceivePortName property, which indicates which receive port the message was received through. Click the new correlation type, and give it a descriptive name in the Properties window. In our example, the correlation type is named ReceivePortNameCorrelationType. In the Orchestration View window, right-click the Correlation Set folder, select New Correlation Set, and specify a name and correlation type. In our example, we create a correlation set named ReceivePortNameCorrelationSet and select ReceivePortNameCorrelationType. From the toolbox, drag the following onto the design surface in top-down order. The final orchestration is shown in Figure 1. Receive shape to receive the initial order message: Configure this shape to use the FinancialTransactionMessage, activate the orchestration, initialize ReceivePortNameCorrelationSet, and to use an orchestration receive port. Loop shape to allow the orchestration to receive multiple messages: Configure this shape with the expression Loop == true (allowing the orchestration to run in perpetuity). Send shape within the Loop shape: This delivers the financial transaction message the destination system. Configure this shape to use an orchestration send port. Receive shape within the Loop shape: This receives the next message (based on the order messages were received) in the convoy. Configure this shape to use the FinancialTransactionMessage, to follow the ReceivePortNameCorrelationSet and to use the same orchestration receive port as the first Receive shape. Figure 1. Configuring a sequential convoy Build and deploy the BizTalk project. Create a receive port and receive location to receive messages from the source system. Create a send port to deliver messages to the destination system. In our solution, we send messages to an MSMQ queue named TransactionOut. In the Transport Advanced Options section of the Send Port Properties dialog box, select the Ordered Delivery option, as shown in Figure 2. Figure 2. Configuring an ordered delivery send port Bind the orchestration to the receive and send ports, configure the host for the orchestration, and start the orchestration. 3. How It Works In this solution, we show how a convoy can be used to sequentially handle messages within an orchestration. The sequential convoy consists of the ReceivePortNameCorrelationSet and the ordered delivery flags specified on the receive location and send port. The first Receive shape initializes the correlation set, which is based on the receive port name by which the order was consumed. Initializing a correlation set instructs BizTalk Server to associate the correlation type data with the orchestration instance. This allows BizTalk to route all messages that have identical correlation type criteria (in our case, all messages consumed by the receive port bound to the orchestration) to the same instance. The Ordered Processing flag further instructs BizTalk Server to maintain order when determining which message should be delivered next to the orchestration. NOTE The adapter used to receive messages into sequential convoy orchestrations must implement ordered delivery. If an adapter supports ordered delivery, the check box will appear on the Transport Advanced Options tab. The Send shape in the orchestration delivers the financial transaction message to a destination system for further processing. The second Receive shape follows the correlation set, which allows the next message consumed by the receive port to be routed to the already running orchestration instance. Both the Send and second Receive shapes are contained within a loop, which runs in perpetuity. This results in a single orchestration instance that processes all messages for a given correlation set, in sequential order. This type of orchestration is sometimes referred to as a singleton orchestration. 3.1. Working with Sequential Convoys The term convoy set is used to describe the correlation sets used to enforce convoy message handling. While our example used only a single correlation set, you can use multiple correlation sets to implement a sequential convoy. Regardless of how many correlation sets are used, sequential convoy sets must be initialized by the same Receive shape and then followed by a subsequent Receive shape. Sequential convoys can also accept untyped messages (messages defined as being of type XmlDocument). You can see how this is important by extending the financial transaction scenario, and assuming that a deposit and withdrawal are actually different message types (defined by different schemas). In this case, a message type of XmlDocument would be used on the convoy Receive shapes. 3.2. Fine-Tuning Sequential Convoys While our example does implement a sequential convoy, you can fine-tune the solution to handle sequential processing in a more efficient and graceful manner. As it stands now, the SequentialConvoyOrchestration handles each message received from the source MSMQ queue in order. This essentially single-threads the integration point, significantly decreasing throughput. Single-threading does achieve FIFO processing, but it is a bit heavy-handed. In our example, all transactions do not have to be delivered in order—just those for a particular customer. By modifying the convoy set to be based on a customer ID field in the financial transaction schema (instead of the receive port name), you can allow transactions for different customers to be handled simultaneously. This change would take advantage of BizTalk Server's ability to process multiple messages simultaneously, increasing the performance of your solution. NOTE In this scenario, you must use a pipeline that promotes the customer ID property (such as the XmlReceive pipeline) on the receive location bound to the sequential convoy orchestration. The PassThru receive pipeline cannot be used in this scenario. Changing the convoy set to include a customer ID field would also impact the number of orchestrations running in perpetuity. Each new customer ID would end up creating a new orchestration, which could result in hundreds, if not thousands, of constantly running instances. This situation is not particularly desirable from either a performance or management perspective. To address this issue, you can implement a timeout feature allowing the orchestration to terminate if subsequent messages are not received within a specified period of time. Take the following steps to implement this enhancement. The updated orchestration is shown in Figure 3. Add a Listen shape in between the Send shape and second Receive shape. Move the second Receive shape to the left-hand branch of the Listen shape. Add a Delay shape to the right-hand branch of the Listen shape. Configure this shape to delay for the appropriate timeout duration. In our example, we set the timeout to be 10 seconds by using the following value for the Delay property: new System.TimeSpan(0,0,0,10) Add an Expression shape directly below the Delay shape. Configure this shape to exit the convoy by using the following expression: Loop = false; Figure 3. Configuring a terminating sequential convoy Finally, you can enhance the solution to ensure that messages are successfully delivered to the destination system before processing subsequent messages. In the current solution, messages are sent out of the orchestration to the MessageBox database via the orchestration port. Once this happens, the orchestration continues; there is no indication that the message was actually delivered to its end destination. For example, if the destination MSMQ queue was momentarily offline, a message may be suspended while subsequent messages may be delivered successfully. Take the following steps to implement this enhancement. The updated orchestration portion is shown in Figure 4. Change the orchestration send port's Delivery Notification property to Transmitted. Add a Scope shape directly above the Send shape. Move the Send shape inside the Scope shape. Add an exception handler by right-clicking the Scope shape. Configure this shape to have an Exception Object Type property of Microsoft.XLANGs.BaseTypes.DeliveryFailureException. Enter a descriptive name for the Exception Object Name property. Add an Expression shape inside the exception handler block added in the previous step. Configure this shape to appropriately handle delivery failure exceptions. In our solution, we simply write the event to the trace log via the following code: System.Diagnostics.Trace.Write("Delivery Failure Exception Occurred - " + deliveryFailureExc.Message); Figure 4. Capturing delivery failure exceptions

Read more ...

BizTalk and MSDTC , DTC , Transactions

Microsoft Distributed Transaction Coordinator (MSDTC) is an important component in a BizTalk environment. Especially in multi-server environment it is mandatory and needs to setup and configured properly before one starts configuring BizTalk features like group, BRE or BAM. During configuration of these BizTalk features, databases like BizTalkMsgBoxDb, BizTalkMgmtDb, BizTalkDTADb (tracking archiving), BizTalkRuleEngineDb, or BAMPrimairyImport and other (BAMStar, ect) are created on database server. MSDTC is component inside Component Services (Windows 2003, Windows 2008). Below is a screen from DTC inside Component Services Windows Server 2008.

 

By right clicking Local DTC you can configure it via three tabs (Tracing, Logging, and Security). To enable MSDTC on Windows Server 2003 or 2008 you can find that on this MSDN page.

Below is a screen from DTC inside Component Services Windows Server 2003.

There are three tabs available and first on is Tracing (this accounts W2K8):

Above screens show default settings (and is first tab shown if you right click local DTC –> Properties). There is an excellent explanation via official Distributed Services Support Team blog that explains what you can do here. Second tab is Logging:

This tab self explanatory I think. The Microsoft Distributed Transaction Coordinator (MS DTC) log file is in the %windir%\system32\Msdtc directory by default. More on choosing where to put log file you can find here. The third tab is the most important one having DTC work properly:

 

Note: DTC account can be changed, look here how and consider if it is necessary. It can be trickydepending on Operating Systems you are working.

Depending on your Operating System you need to set the following settings (above shows the default settings on Windows 2008).

Configuration Option	Default Value W2K3	Default Value W2K8	Recommended value
Network DTC Access	Enabled	Enabled	Enabled
Client and Administration
Allow Remote Clients	Disabled	Disabled	Disabled
Allow Remote Administration	Enabled	Disabled	Disabled
Transaction Manager Configuration
Allow Inbound	Enabled	Enabled	Enabled
Allow Outbound	Enabled	Enabled	Enabled
Mutual Authentication Required	Disabled	Disabled	Enabled if all remote machines are running Win2K3 SP1 or XP SP2 or higher.
Incoming Caller Authentication Required	Disabled	Disabled	Enabled if running MSDTC on cluster.
No Authentication Required	Disabled	Disabled	Enabled if remote machines are pre-Windows Server 2003 SP1 or pre- Windows XP SP2.
Enable XA Transaction	Disabled	Disabled	Enabled if communicating with an XA based transactional system such as when communicating with IBM WebSphere MQ using the MQSeries adapter.

To validate the connection between BizTalk Server and SQL Server machines you can use DTCPing. Also take into consideration that firewall can block access. To configure firewall on W2K3 you can gohere and for W2K8 here.

Besides DTCPing there is also a tool called DTCTester. Latter utility is to verify transaction support between two computers, if SQL Server is installed on one of the computers. The DTCTester utility uses ODBC to verify transaction support against a SQL Server database. For more information about DTCTester see How to Use DTCTester Tool. DTCPing is to verify transaction support between two computers, if SQL Server is not installed on either computer. The DTCPing tool must be run on both the client and server computer. For more information about DTCPing, see How to troubleshoot MS DTC firewall issues.

To troubleshoot MSTDC you can also find valuable information at this MSDN page and summary of other useful tools here. I hope that if you are building a multi-server BizTalk environment that this information gathered here can help you setup and configure MSDTC properly before you start configuring BizTalk features.

Source SteefJanwiggersBlog

Read more ...