This post was originally published here
It’s fair to say that we all know the default Database Lookup functoid. It allows you to connect and extract information from a database. This functoid requires four input parameters in the following order:
- A lookup value – value for which to search in the specified database, table, and column (next parameters);
- 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 – name of the table in the database in which to search
- A column name for the lookup value – name of the column in the table in which to search – the first parameter
The functoid is quite simple to use, however, the main problem that developers face when they use it, refers to the second parameter: the connection string.
How to know the correct value of the connection string to be set in the Database Lookup Functoid
Is hard to remember the correct syntax/value to specify in the connection string to be used inside the Database Lookup Functoid.
For you to be sure that you don’t make any mistakes, which could lead to future waste of time by diagnosing and rectifying the connection string, the simple and easy way to guarantee that we are using the correct connection string value – but also for not having to remember this by head – is to:
- Create a simple Universal Data Link (.udl) File
- Set the OLE DB provider connection parameters
- And finally, test the connection to check if everything is correct
To accomplish this, we need to:
- Create a text file and name it “ODBCConnectionTest.udl” on your file system
- Preferably on the BizTalk Server machine
- The name of the file is not important, the important part is the extension, it must be “.udl”
- Double-click the Universal Data Link file that 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:
- The SQL Server instance that hosts the database
- The authentication method: Use Windows NT integrated security or Use a specific user name and password
- And finally, the database name to which you want to connect
- After specifying these properties, you then should click on “Test Connection…” button to attempt a connection to the specified data source. If no connection is made, review the settings. Otherwise, click “Ok”
Now that we have our Universal Data Link file created and properly configured, you can open the UDL file in Notepad and you will have access to the connection string value that you should use inside the Database Lookup Functoid. You just need to copy and paste it inside the second parameter of the Functoid.
Note: you should paste the connection string statically inside the Database Lookup Functoid but that is a different topic that we will address in another BizTalk Server Tips and Tricks.
I hope this small trick will the useful, enjoy and stay tuned for new BizTalk Server Tips and Tricks!
