BizTalk: SQL Adapter and SQL Ports: User Interface: Review

BizTalk: SQL Adapter and SQL Ports: User Interface: Review

I'm considering only working with stored procedures, not SQL commands nor Updategrams. Generating the SQL port artifacts with “Add Adapter Wizard” is out of scope this article.

Two-root schemas

The SQL ports use the Two-root schemas, one root for Request and one for Response. We’ve got one root empty if we use the stored procedure without input parameters.

Why? The one reason I can see is the logical grouping two schemas working with one port. From one side it logically links those schemas; from other side it seriously complicates the issue. Now we have to use the multi-part message types, have to manually prepare them after the schema generating procedure. Such preparing includes copying the multi-part message types and the special port types, using these messages types, to our orchestration. In most cases we should rename this schema and copy it to other project. It is not a straightforward process.

Why there is not used the standard Request-Response object model with two separate schemas? In this case we should manually create in the orchestrations the ports working with such schemas. There are not the multi-part message types, not all error prone steps around.

Anyway, the SQL ports use the Two-root schemas.

One-way and two-way ports

The stored procedures always return the data. In the simplest case it is the integer value (by default 0 means Success).  That means all SQL ports (working with stored procedures) should be two-way ports.

Let see, is it true or false.

Send ports

We can create one-way and two-way SQL Send ports.



(Pict. 1. One-Way Send Port Properties window)



(Pict. 2. Solicit-Response (Two-Way) Send Port Properties window)


As you can see the only difference is the two-way port can use different pipelines for send and receive ways when the one-way port use one pipeline for both directions.

Receive ports

For the SQL Receive ports we can create only one-way ports.


Undercover we always work with two-way SQL ports (ports which work with stored procedures) no matter how they are called in BizTalk.

SQL Send ports

When we click the “secret” button […] in the URI field of the Send Port Properties window (Transport tab) we’ve got the “SQL Transport Properties” window.

(Pict. “SQL Transport Properties” window of the Send port)

SQL Receive ports

When we click the “secret” button […] in the “URI” field of the “Receive Port Properties” window (Transport tab) we’ve got the “SQL Transport Properties” window.

(Pict. 1 “SQL Transport Properties” window of the Receive port)


After configuring the “Connection String” parameter we’ve got the value of the “URI” field. It is compounded from a protocol name – “SQL://”, a server name – “localhost”, and a database name – “Court_Prod”.

There is another “secret” button […] in the “SQL Command” field.


(Pict. “Import Information from a generated schema” window)

Does the “Project” drop-down field hold the project names? No. It holds the names of the deployed assemblies and the public BizTalk assemblies like Microsoft.BizTalk.GlobalPropertySchemas or Microsoft.BizTalk.DefaultPipelines.

We should choose the schema. It should be the schema generated by Add Adapter Wizard.

The generating schemas for SQL port is out of scope this article, but the name of this wizard is the other naming odd of the BizTalk. This wizard does not add adapter. We are adding adapters from BizTalk Administrative Console. This wizard generates the objects (artifacts) for the SQL Port. In BizTalk 2006 it is called “Add Adapter Metadata Wizard” but anyway it adds metadata not for adapter but for the port. The adapter metadata should be the “default” data for all ports this adapter type not for the specific port.

After this we have the modified “SQL Transport Properties” window.

(Pict. 2 “SQL Transport Properties” window of the Receive port)

There are three more parameters: “Document Root Element Name”, Document Target Namespace”, and “SQL Command”.

Take in mind these three fields are Editable, you can change it by mistake. Why it so? If these fields fill up automatically why we need to change them by hand?

We have to take one more step to configure The Receive port.

(Pict. 3 “SQL Transport Properties” window of the Receive port)

We have to change the “URI” field adding to it some unique name.

Comparing of the “SQL Transport Properties” window of the Receive and Send ports


(Pict. “Adapter Properties” of the Send port)


(Pict. “SQL Configurations” of the Receive port)


It’s quite amazing how different they are and how different they are managed. At the same time they have the same meaning.

Differences in naming:


Name of parameter group:

Send port (SP):        “Adapter Properties”

Receive port (RP):    “SQL Configuration”

Response Root Element Name:

          SP:     “Response Document Root Element Name”

          RP:     “Document Root Element Name”

“SQL Command” and “URI” in RP:

          SP:     N/A

          RP:     Editable

Differences in management:


Fill in:

RP:     all fields are filled in automatically

SP:     only a “Connection String” is filled in automatically. “Document Target Namespace” and “Response Document Root Element Name” we should copy-past from the schema or fill up manually.


SP:     can not change it. It can be non unique. (It schould be easy make it unique by add the root name, for example.)

RP:     should (!!!) change it to make unique. Why is it so in this case?


The receive port is initiated “outside”. For example a file is created in the FILE receive location. The file listener service works undercover. Usually the BizTalk do not give us the possibilities to manage the time parameters of this service.

But for some other Receive ports we can change the parameters of these outer services. (In BizTalk 2006 we can change the polling interval for FILE transport too.)

The SQL Receive Ports work by executing the stored procedure or SQL command. This process is called “Polling”.

For SQL Receive ports we can change the “Polling parameters”.


(Pict. “Receive Configuration” of the Receive port)


For some reasons they are named “Receive configuration”, although all parameters of this Receive port are “receive”. Obviously there should be “Polling parameters” or “Polling configuration”.

SQL Adapter

From the “BizTalk Administration Console” we can change some of the SQL port parameters for each Adapter handler.

(Pict. “SQL Transport Properties” for Send Port Handler from the “BizTalk Administration Console”)


(Pict. “SQL Transport Properties” for Receive Port Handler from the “BizTalk Administration Console”)


The Adapter Handler properties work as default values for all Ports. (I thougt so, but in realaty they are the default values for Dynamic ports.) From this point of view the parameters for Send Port Handler look strange, aren’t they?


The user interfaces of the Send and Receive parts of SQL ports are not coordinated. They use different names for the same parameters. They use different user interfaces to change the same parameters. Maybe all this naming has an underlying basis but there are no topics in the documentation to explain it.

The user interfaces of SQL ports are complicated and hard to understanding.




If you have comments, please, give me a feedback!

Leonid Ganeline
BizTalk Developer










Print | posted on Tuesday, March 14, 2006 2:37 PM


No comments posted yet.
Post A Comment