As most of you already know, last month I presented a session at the BizTalk Summit 2015 London event about BizTalk Server tips and tricks for developers and admins (You can check the video recording of my session here)
It was a lightweight session (30 minutes) about useful tips that we can use in our daily work. Because it was a small session, I didn’t have enough time to cover everything, so I ended up creating a “Director’s cut…” session with additional tips that you can check in the presentation slides here.
Today, and because I already received some emails regarding this topic, I will address the Database Lookup functoid.
We can use the Database Lookup functoid to extract information from a database and store it as a Microsoft® ActiveX® Data Objects (ADO) recordset. This functoid requires four input parameters in the following order:
- Parameter 1: A value for which to search in the specified database, table, and column.
- Parameter 2: The full connection string for the database with a provider, machine name, database, and authentication (an ActiveX Data Objects .NET (ADO.NET) connection string).
- Parameter 3: The name of the table in the database in which to search.
- Parameter 4: The name of the column in the table in which to search.
The functoid is actually quite simple to use; however, the main problem that developers face when they use it refers to the second parameter: the connection string. And why?
📝 One-Minute Brief
Learn how to easily manage connection strings in the BizTalk Database Lookup functoid. This guide covers two essential tips: first, using a Universal Data Link (.udl) file to generate the correct connection string without memorizing syntax; second, avoiding hard-coded values by using custom functoids to pull configurations from SSO or BRE. These methods simplify environment deployments and reduce maintenance overhead.
First: What is the correct value for the connection string?
I always find it hard to remember the correct value for the connection string to be used inside this functoid.
The Easiest way to make sure we are using the correct connection string value and do not have to remember this by heart is to create a simple Universal Data Link (.udl) File… set OLE DB provider connection parameters and test the connection to check if everything is correct.
To accomplish this, we need to:
- 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 name of the file is not important; the important part is the extension, which 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.
- In my case, it is Microsoft OLE DB Provider for SQL Server.
- In the Connection tab, specify:
- Where your data is located, typically the server and database name.
- How to connect to it using an OLE DB provider: Use Windows NT integrated security, or Use a specific user name and password.
- In this particular case, you need to provide the SQL Server, the database name we want to connect.
- Then you can and should click on the Test Connection… button to attempt a connection to the specified data source. If no connection is made, review the settings. Otherwise, click Ok.
After opening the ODBCConnectionTest.udl file in Notepad, 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
Second: What is the best way to set the connection string inside the Database Lookup Functoid?
Hard-coding the SQL connection strings might lead to maintenance overhead and serviceability issues.
Important considerations:
- You shouldn’t hard-code this value directly in the functoid; otherwise, it will be a nightmare when you deploy this to a different environment.
- You can, and you should store this parameter in a different storage location (SSO, Registry, or others) and 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 the Windows Registry.
- SSO Config Get Functoid: This functoid allows you to get configuration parameters from the SSO Database.
- Rule Engine Config Get Functoid: This functoid allows you to obtain a definition value from a Vocabulary in the Business Rules Engine.
In my personal opinion, I advise you to use SSO or BRE to store these configuration parameters. All of these custom functoids are available in the BizTalk Mapper Extensions UtilityPack.
Download
You can download the source code from GitHub:
Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son.
Thanks! Very usefull