Configuring Dynamic SQL Port in BizTalk 2009

This is from Ganesh Kumar's BizTalk Blog:
http://geekswithblogs.net/GaneshKumar/archive/2009/09/03/dynamic-sql-port---new-in-biztalk-2009.aspx

Until the days of Biztalk 2006R2, the SQL Port was Static only and did not support dynamic ports.

BizTalk 2009 fully supports Dynamic SQL Ports, both for the regular out-of-the-box SQL Adapter and the new WCF SQL adapter.

For the regular SQL Adapter (which is being deprecated by microsoft), three new Message Context properties are available:

(SQL.ResponseDocumentRootElementName)
(SQL.DocumentTargetNamespace)
(SQL.ConnectionString)
Configuring these properties, together with
(Microsoft.XLANGs.BaseTypes.Address)="SQL://" +Servername/ + Databasename/
creates a fully functional Dynamic SQL Adapter.


[This is directly from Microsoft: http://msdn.microsoft.com/en-us/library/dd788439(BTS.10).aspx]

In BizTalk Server, you can configure dynamic ports for a WCF-Custom adapter. Because the SQL adapter is a WCF-based adapter, you can dynamically configure a port for the SQL adapter by using message context properties.

For the SQL adapter, the URI, action, and binding may be determined from a property on an incoming message, and then specified in the Expression shape, as shown in the following example:

CopyRequest2=Request1;
Request2(WCF.Action)="TableOp/Insert/dbo/CustomerTable";
Request2(WCF.BindingType)="sqlBinding";
Request2(WCF.UserName)="myuser";
Request2(WCF.Password)="mypass";
SendPort(Microsoft.XLANGs.BaseTypes.Address)="mssql://sql_server/my_instance/my_database";
SendPort(Microsoft.XLANGs.BaseTypes.TransportType)="WCF-Custom";


Note:
If you are using a WCF-SQL adapter in BizTalk Server Administration console, you can also specify the transport type as SendPort(Microsoft.XLANGs.BaseTypes.TransportType)="SQLAdapter", where SQLAdapter is the name with which you added the WCF-SQL adapter in BizTalk Server Administration console.

In the preceding example,

•Request2 message is being created from Request1 message. Both messages map to an operation schema, which is generated using the Consume Adapter Service Add-in.


•SendPort is the name of the logical send port in the BizTalk orchestration.


The Expression shape is part of the BizTalk orchestration. Deploying the orchestration also creates a WCF-Custom send port.

For more information about configuring dynamic ports, see “Configuring Dynamic Send Ports Using WCF Adapters Context Properties” at http://go.microsoft.com/fwlink/?LinkId=106735.