SQL Adapter & BizTalk

SQL Adapter & BizTalk

The SQL adapter retrieves an XML message from the BizTalk MessageBox and ultimately do operations in a remote SQL Server database, all based on the contents of the XML message. The SQL adapter enables BizTalk to poll a SQL Server database via a SQL query or stored procedure to retrieve rows as one or more XML messages. Conversely, the SQL adapter can be used to insert, update, or delete rows in SQL Server tables using the SQL updategrams or by invoking predefined stored procedures.

Major Concern while dealing with SQL adapter:
a) Same Record should not be fetched again and again:
 Use Record status flag which is by default 0 means record is not yet picked.
 Use data Range
b) Handle the Exceptions which may occur while doing operations into a SQL table.

SQL Adapter at Send side:
1. Updategram(Insert\Update\Delete)
a. Add Generated items  Add Sql Adapter 
i. conn string (Server Name,DBName, Port Name)
ii. Target Namespace (very imp)
iii. Request & response document root element name
iv. Select Updategram  Select (Insert\Update\Delete)

This will create two artifacts in our solution. 1. Orchestration 2. Schema
The structure of the schema generated will be different for insert, update and delete.
The key concept while using Updategram is that we have a before and after section in the updategram message that SQL Server uses to figure out what to do with the data. For instance, having a record in the before but not in the after is equal to a Delete operation in SQL Server. Also, an after record with no before equals an Insert and a record in both the before and after results in an Update.

Again in admin console we need to give the connection string and other details while configuring the SQL Send Port.

Note: To modify multiple tables, we can run the wizard multiple times and then copy the generated schemas into a single shema. However this is achievable using updategrams but we prefer stored procedures for it.

2. Stored Procedure
Same as above except in point iv we select Stored Procedure, select the spname and generate.

This will also create two artifacts one orchestration and one schema.

Advantages using Stored Procedure over updategram:
1. SELECT\INSERT\UPDATE\DELETE possible.
2. Procedure can be called to return data from a DB.
3. We can have input params to the sp but could take the entire contents of the xml message and parse the contents of the message.


SQL Adapter at Receive side:

Steps are same as above in Stored Procedure.
Since queries in SQL returns rowsets, we need a mechanism to retrieve records in xml format.

FOR XML AUTO, XMLDATA, ELEMENTS is used for that.

For xml auto returns in xml format.

Xml data is required only while running the wizard to generate the schema for the resultset from the sp. While testing we should remove this clause.

IMPORTANT: BT uses the root element name and the namespace to route the inbound xml file. If both does not match to the xsd file created, it will give routing errors.

For XML Explicit gives developer greater level of control in the resulting XML structure that is returned to BizTalk.

Adapter Configurations:

1. Poll while data found: indicates whether to continue polling for new records in additional batches or whether to submit a single query result for the entire polling window.
2. Polling interval: Identifies how often to poll for records.
3. Polling unit of measure: Identifies the unit of measure for polling.
4. connection string
5. Document Root element name
6. document Target namespace
7. SQL command
8. URI : Identifies a unique identifier for the receive location. This value is autogenerated.

I think this is enough of SQL adapter basics as far as its connectivity to BizTalk engine is considered. Lately I have come across a very good article on the same at this:

http://blogs.msdn.com/richardbpi/archive/2005/06.aspx