TopXML Oracle Adapter Dynamic "like" Send Port

A colleague and I recently ran into a situation where we needed to determine the Oracle Connection string at runtime. This may sound like an odd requirement, but the 3rd party application that populates this database has been configured for redundancy in an a-typical fashion. It has not been set up in a fashion like a SQL Cluster...that would be too easy :-). So instead of letting an infrastructure component(like Windows Clustering) determine which instance is the "Primary", their software performs this function.

I have set up a POC that will walk you through this configuration.

The first thing that you are going to want to do is add a reference to your project. The DLL that you want to include is "BTSUtils.Adapters.Databases.Properties.dll". You can find this file in your \BizTalk Utilities\Database Adapter\Bin.

This will allow you to update the Connectionstring context property.

Below is an image of my orchestration. The orchestration doesn't do anything all that spectacular. The majority of the orchestration is for demonstration purposes.

Basically, a file is passed in that has data which will be used as part of the Oracle Request. So a simple map will take the value from the incoming document and map it to the Oracle Request.

Once, we have the Oracle Request document, we need to update the context property as part of the Message Assignment Shape. I have updated the
BTSUtils.Adapters.Databases.Properties.Transmit.Connectionstring and assigned a new value.

So obviously hard coding the connection string in your orchestration is not a good idea, but remember this is just some POC type code. In our requirements, we need to be able to connect to one of two data sources, but have to determine this at run time. Some options for storing these connections strings may be the BTSNTSvc.exe.config(clear text warning!) or in the SSODB(secure) and extracting the values via the SSO API.

Once this property is set, I submit the Oracle Request message and wait for a Response. The Expression shape in my orchestration, is simply a trace statement. I am interested in knowing how may nodes are returned as a result of my query.

After this log point, I simply drop the results of the query to a folder.

So if I modify the connection string inside of the Orchestration, then what does the Send Port configuration look like?

Interestingly enough, you do not have to populate the "Connection String" property with anything meaningful what so ever. You do need to provide it with a value in order to successfully complete the configuration though. I have decided to populate this property with the associated Macro. This way an Ops guy can look at it and figure out that the value is populated elsewhere.