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:
And for BizTalk Server Developers:
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.
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:
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:
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.
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:
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
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:
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:
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:
That you can find available on the BizTalk Mapper Extensions UtilityPack.
Stay tuned for the following BizTalk Server Best practices, Tips, and Tricks.