Dynamic Send Port SQL Adapter

Question

 

I’m trying to configure a dynamic SQL port in orchestration, but in SQL adapter port case I can only set the URI (SQL://servername/databasename) dynamically in expression shape before the dynamic port, put it didn’t work, so where I can set the connection string dynamically. Anyone have any idea?.

Answer 1

As http://msdn.microsoft.com/en-us/library/ms935658.aspx

[

Note
The Adapter for SQL Server does not support dynamic port configuration. Any connection information must be hard coded at design time.

]

Answer 2

The beginning of this article http://msdn.microsoft.com/en-us/library/ms915985.aspx says that there is a dynamic SQL send ports "The dynamic SQL send ports use all of the properties set on the send handler".

 

I have reached a work around; please tell me what do you think about it?

 

There are many types of adapters under the node Platform Settings in BizTalk Server Administration including the SQL Adapter; you will notice that you can configure (on Send Handler and the Receive Handler properties of the SQL Adapter) the Connection String, the Document Target Namespace, and the Response Document Root Element (In the SQL Adapter), but if you do so it will be a global configuration on all Send Ports.

 

In static SQL port you configure an inherited instance of the SQL adapter with specific Connection String, the Document Target Namespace, and the Response Document Root Element, but what about the Dynamic SQL send port how can you configure it with the before mentioned properties?

 

The Dynamic SQL send ports use all of the properties set on the send handler of the adapter (assigned dynamically in the orchestration but without the three properties) and to avoid the global configuration when configuring the default shipped SQL Adapter; you should implement a custom SQL Adapter, install it in BizTalk and then configure the three properties, and then all you have to do is set the URI with the name of your custom adapter e.g. URI = custom adapter name://server name/database name.

 

Answer 3

Hi Islam,

What do you mean by Implement a custom SQL Adapter in the context of dynamic sql send port from your note above? Do we need to create a custom SQL adapter or Install SQL adapter from the biztalk installer in custom mode?

Thanks,

Rick