Hi! Today I´m going to talk about updategrams when using SQL Adapter as an alternative to send a message!

The SQL Send Adapter supports two choices for sending messages: Updategrams or Stored Procedures.

An updategram is a formatted XML fragment made up of several blocks. Each of these blocks serves a specific purpose. It is the arrangement of these blocks that determines whether the updategram will insert, update, or delete the record. The basic structure of the updategram is shown here:

<root>
 <sync>
   <before>
     <TableName col1="value" col2="value" …/>
   </before>
   <after>
      <TableName col1="value" col2="value" …/>
   </after>
</sync>
</root>

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

• The <before> block is used to describe the actual state of the rows. It acts like a WHERE clause.

• The <after> block is used to describe the new state of the rows. It acts like a WHERE clause.
• The <sync> block is a container for the <before> and <after> blocks. It also represents a transactional unit.

It is possible to have more than one <before> and <after> blocks within a <sync> block. In this case the whole operation is transactional. It is also possible to have more than one <sync> block, thus allowing independent transactions.

How do you specify if the updategram will insert, update or delete records?

The answer to this question as I mentioned earlier is the order of the <before> and <after> blocks:

• When a message appears with only an <after> block, and no corresponding <before> block, the updategram performs an insert operation

• When a message appears with only a <before> block, and no corresponding <after> block, the updategram performs a delete operation

• When a message contains both a <before> and a corresponding <after> block, the updategram performs an update operation

In this figure there is an updategram example for each operation:

Figure (a) shows a schema for inserting rows in Customer table. The columns for this type of row are CustomerId, Surname, Name and DocumentNumber.

Figure (b) shows a schema for updating rows in Customer table. The columns to be updated are surname, name and documentNumber and the where condition involves the customerId.

Figure (c) shows a schema for deleting rows in Customer table. The where condition involves the column customerId.

Although in the example I have a separate updategram for each operation, this can be combined within the same updategram to perform more complex operations!

Today I’ve been trying to receive a message with a string field which value exceeded 256 characters. This was not a problem when defining the schema. But when I needed to get this field property promoted I got the following error:

There was a failure executing the receive pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "Party resolution" Receive Location: "SQL://MyMachine/Database/" Reason: The property ‘Field1′ has a value with length greater than 256 characters.

After doing some research, I’ve found in Biztalk 2006 Documentation that there are some limitations when using Property Promotion:

• Promoting a string value that is longer than 256 characters in length may cause overflow exceptions at run-time.

• Promoting nodes of data type xs:integer that require more than 28 digits may cause overflow exceptions at run time (due to the internal use of the .NET data type System.Decimal, which has a 28-digit limit). You can work around this limitation by interpreting and promoting very large numeric values as strings, thereby increasing the effective limit to 256 digits.

• Promoting nodes of data type xs:long or xs:unsignedLong is not supported at run time and may lead to unexpected results. To avoid this issue, consider changing the data type of such promoted nodes to xs:string.

From now on, I will keep these limitations in mind when working with property promotion. Hope this helps!

These last few days I’ve been working with the SQL Adapter for receiving messages in my orchestration and I’ve got stuck in a peculiar situation: one of the columns I was retrieving has an XML string as its value. The situation was that I wanted to create another message from that XML string after the reception of the SQL message.

In this post I’ll show how to create a message in an orchestration from an already created XML string.

In order to understand better the situation, let’s describe the scenario: 

• Suppose we have a SQL Table with three columns, for example: Id (uniqueIdentifier), occursOn(DateTime), configXML (varchar).
• Using the Wizard, I created the schema for retrieving the rows from the SQL table.
• Once received the message, which involves the three columns mentioned above, I wanted to create a new message from within the orchestration using the configXML value received in the SQL message. 

This can be achieved with the help of System.Xml.XmlDocument class. This class has a method called LoadXml( string xml ) which allows us to create an XmlDocument from an already created XML string. After loading the XML from the string, we can set this document to our message in Biztalk; this is possible because every message in Biztalk derives from XmlDocument. 

Here are the steps to follow to construct a message from an already existing XML string:

1.       Create the schema file for your already existing xml string

2.       Add a message of the type created in step 1 to your orchestration

3.       Add a variable of type System.Xml.XmlDocument to your orchestration

4.       Add a construct shape in order to construct your message of step 2

5.       Add an expression shape inside the construct shape with the following expression:  

In this example, varXMLDoc is the variable created in step 3; MySQLMessage.configXML is the XML string previously received in another message; and MySecondMessage is the message created in step 2.

That’s it, hope this to be useful!