BizTalk Server Best practices, Tips, and Tricks: #4 the Database Lookup Functoid

Welcome once again to another BizTalk Server Best practices, Tips, and Tricks blog post! In my previous blog post, I talked about some essential tips and tricks for BizTalk Server administrators:

• #1 What do RosettaNet, ESB, and UDDI have in common?
• #2 BizTalk MarkLog tables

And for BizTalk Server Developers:

• #3 Creating Custom Functoids

Today, I’m going to stay on the same topic – Functoids – and speak about another good Best practice, Tips and Tricks for BizTalk Server Developers: Creating Custom Functoids. 

#4 The Database Lookup Functoid

The Database Lookup Functoid is one of the many out-of-the-box Functoids that you can use inside BizTalk maps. You can use this functoid to extract information in runtime from a database and store it as a Microsoft ActiveX Data Objects .NET (ADO.NET) recordset. Then in conjugation with the Value Extractor functoid and the Error Return functoid, you can either:

• Extract data from a specified column in a recordset returned by the Database Lookup functoid. This functoid requires two input parameters:
  • A link to the Database Lookup functoid and a column name.
• Capture error information, such as database connection failures, that occur during run time. This functoid requires one input parameter:
  • A link from the Database Lookup functoid.

The Database Lookup Functoid is one of the ten existing functoids in the Database functoids category— Value Extractor, Error Return,  Format Message, Get Application ID, Get Application Value, Get Common ID, Get Common Value, Remove Application ID, Set Common ID, and Database Lookup.

It is fair to say that almost all BizTalk Server developers know the existence of the default Database Lookup functoid, and I’m almost sure that several of us already use it in one or more of our maps. The configuration of this functoid is quite simple. It will require four input parameters in the following order:

• A lookup value – value for which to search in the specified database, table, and column (last parameter);
• A database connection string – The full connection string for the database with a provider, machine name, database, and authentication (an ActiveX Data Objects .NET (ADO.NET) connection string);
• A table name – the name of the table in the database in which to search;
• A column name for the lookup value – the name of the column in the table in which to search the first parameter;

Despite being quite simple to use, the main problem of this functoid which makes developer life harder when they use it refers to the second parameter: the database connection string.

• What is the correct value of the connection string that we need to put in the database connection string parameter? 
• Where to store this value?

What is the correct value for the connection string?

I always find it hard to remember the correct syntax/value to specify in the connection string to be used inside the Database Lookup Functoid. For me, the easiest way to ensure we are using the correct connection string value and not having to remember this by head is to create a simple Universal Data Link (.udl) file and set the OLE DB provider connection parameters. On that file, we can also test the connection to check if everything is correct before configuring it on our map functoid – which will be more difficult to debug and troubleshoot.

To accomplish this, we can:

• Navigate to a folder in your system, which can be on the desktop or preferably in a folder under your BizTalk Visual Studio solution. Let’s call it: Resources.
• Create a text file and name it ODBCConnectionTest.udl
  • The file’s name is not important, the important part is the extension. It must be .udl
• Double-click the file you just created.

• On the Provider tab, select the appropriate OLE DB provider for the type of data you want to access, and then Next
  • Assuming that we want to connect to a SQL Server database, it will be Microsoft OLE DB Provider for SQL Server

• In the Connection tab, specify:
  • On the Select or enter a server name property enter the SQL Server instance that hosts the database we want to connect.
  • On the Enter information to log on to the server property, select Use Windows NT integrated security or Use a specific user name and password
  • And on the Select the database on the server property, type the database name to which you want to connect

• After specifying these properties, you should click the Test Connection button to attempt a connection to the specified data source to validate the configuration. If no connection is made, review the settings. Otherwise, click Ok.

After successful connectivity,  open the ODBCConnectionTest.udl file with Notepad (or another text editor), and you will find the connection string value that you can copy and use in the second parameter of the Database Lookup Functoid.

This will lead us to the second problem that you can face using this functoid: using the connection string statically inside the Database Lookup Functoid

Where to store the connection string?

Hard-coding the SQL connection strings inside our Database Lookup is not an option! This will lead to maintenance overhead and serviceability issues. The connection string is different from one environment to another environment – or at least it should be! Even if it was the same, at some point in time, we might need to redirect/reconfigure to a new environment.

So it is a settled point that you should not Hard-code this value directly in the functoid. Otherwise, it will be a nightmare when you deploy this to a different environment.

Saying that the question is: What is the best way to set the connection string inside the Database Lookup Functoid?

Well, you, in fact, only have a good option. You need, and you should store this parameter in a different and external location (outside the map). One of the first things that we might think of is to store this information as a key/value pair in a config file, but that is also not a good choice because:

• If you choose to use a custom config file, then you need to hard-code the file path location, or you need an additional location/component to configure the path to the config file.
• The other option you have is to store this in the BizTalk Server configuration file – BTSNTSvc.exe.config – but that also has huge disadvantages:
  • You have two BizTalk Server configuration files in which you need to maintain the same configurations:
    • BTSNTSvc.exe.config for 32-bits
    • BtsNTSvc64.exe.config for 64-buts
  • And if you have more than one BizTalk Server box, you need to replicate these settings in each server.
  • Not to mention if it has passwords, they will be in clear text inside the config files, which is not good for security.

A better option would be the Registry, you could create an entry and store all your key/values configurations. Nevertheless, if you have more than one BizTalk Server box, you also need to replicate these settings in each server

So, why not a custom database? Well, for the same reasons as the Database Lookup functoid. If you use a custom database to store configurations, you also need something where to configure access to this custom database of configurations – the connection string – because that will be different from the dev environment to the production environment.

The ideal place should be a central place, easy to access, secure if possible, and that doesn’t need additional configurations to access. Out of the box, you have two options:

• Business Rules Engine, surprise? Yes, you can store keys/values that you can use for config your application by using Vocabularies.
  • However, the data will not be encrypted. So, it is less secure than the next option.
• SSO database! It is where the secrets of the BizTalk Server are stored. It is a central database that you can access directly without any additional configurations. And it has the capabilities to store keys/values.

If you are thinking, yes, it is a good idea, but how can I access these systems/components from my BizTalk map?

Well, that is easy. You can get this value using a scripting Functoid or custom functoid, which can then be linked to the Database Lookup Functoid, like the:

• • BTSNTSvc Config Get Functoid: This functoid allows you to get configuration parameters from BTSNTsvc.exe.config. If there is no section specified, the functoid reads from the AppSettings.
  • Windows Registry Config Get Functoid: This functoid allows you to get configuration parameters from Windows Registry.
  • SSO Config Get Functoid: This functoid allows you to get configuration parameters from SSO Database.
  • Rule Engine Config Get Functoid: This functoid allows you to obtain a definition value from a Vocabulary in the Business Rules Engine.

That you can find available on the BizTalk Mapper Extensions UtilityPack.

Stay tuned for the following BizTalk Server Best practices, Tips, and Tricks.