Working with BizTalk and don’t need to interact with SQL database…….nah…that’s not possible. I googled for database Lookup functiod but no link gave me a simple sample as to what this functiod does and other functiods which are linked to this Database Lookup Functiod. So I just thought to pen down few Standard database functiods available with BizTalk
First let’s start with.
Database lookup Functiod.
I created a table called “NishTable” with fields and values as below.
Fig1
Then I create an Empty BizTalk Project with
1 Map and 2 Schemas.
Create the Schemas as shown in the Fig2.
Just drop in the Database Lookup Functiod on the Map as shown in Fig2.
Fig2
Let’s Configure this Database Lookup Functiod.
For this Sample I won’t pass any input …it would be harcoded.
Right click on the Functiod…properties…
1 parameter-Just type 2
2nd Parameter-Sql connection String.
3rd Parameter-Table name-NishTable (Created before)
4th Parameter-Column name.-EmpID
What these configured values means is
“Select _ from NishTable where EmpID=2”.See fig 3 for details.
Fig3
Now we need to fill in the blank space in the above sql query as to what should be selected. So we use value extractor functiod and configure it as soon in fig4.
EmpName is the field whose value I want…So I type “EmpName” as second parameter.
Mapping is simple 1 to 1 map.
Fig4
This way a Database Lookup functiod works. You can test your map.
Now what about error handling. If the sql connection is down?
So let’s use “Error Return functiod” here.
Just link the functiods as shown.
“Error Return functiod” requires only 1 parameter that should be the output of Database Lookup functiod.
Refer fig 5 for more details.
Fig 5
Just to test it I stopped my Sql server and I can see the output as below.
Fig6
So this was Database lookup Functiod. We can use this instead of using a .net component inside map to get the values from database.
