Eric's BizTalk 2004/2006/2009 blog

Hijacking the Database Lookup functiod

Here is the functiod description for the Database Lookup functiod:

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 the following four input parameters in this order: a lookup value, a database connection string, a table name, and a column name for the lookup value.

1. Lookup Value
2. Database Connection String
3. Table Name
4. Column Name

I did not want to create a view, as I don’t have permissions to add objects to that database, but I could not simply get the value from one table, I needed to join two tables together.

The answer was SIMPLE!

Underneath the covers, the statement that is being called is:

using (OleDbCommand command = new OleDbCommand("SELECT * FROM " + table + " WHERE " + column + "= ?", helper.Connection))
{
...
}

WAIT A MINUTE! I am not limited to only a table name in the table variable of the OleDbCommand method, I can put an entire join statement in argument 3 of the Database Lookup functiod.

Here is what I had for the arguments:

1. Source Data
2. Connection Information (in this case I just pointed to a udl file): ‘File Name=D:\Eric\Warehouse\Schemas\connection.udl’
3. Table name, which has the join: ‘EdiPartnerContacts INNER JOIN bts_party ON EdiPartnerContacts.PartyId = bts_party.nID’
4. Column that has the input value: ‘nvcName’

So in essence I had created the following query using the DBLookup functiod

SELECT     * 
FROM         EdiPartnerContacts INNER JOIN 
                      bts_party ON EdiPartnerContacts.PartyId = bts_party.nID

WHERE       nvcName=?

I then pulled the Email column from the data in a Value Extractor that was connected to the Database Lookup functiod

posted @ Wednesday, May 06, 2009 3:42 AM