Hi,

I have requirement where I have a source EDI message with LIN loop(repeating) mapped to repeating record in the destination.

There is a look up for every LIN01 element in the source from a table and mapped to the destination element.

If there are 50 LIN records with LIN01,  50 times I am calling the database to fetch the resultset and mapping to destination.

I dont see it a good approach , because there will be n number of database call, number of times we have LIN.

(Note : Every LIN01 has a different value) 

Can anyone help with a better approach in mapping??

Thanks,

Hi Varun,

#1) You can make use of BizTalk Database lookup functoid with caching feature provided by codeplex.

This functoid can be used like the Database lookup functoid that ships with BizTalk. It will execute a query against a given database and cache the result using the System.Web.Caching.Cache with a sliding expiration of one minute.

How it works? Refer: Database lookup functoid with caching feature

#2) You could write a custom static class that has a static method in it that takes the LIN01 as a parameter. The method could then be called from a scripting functoid.

Refer Saravanan's article on the same Implement Caching for your BizTalk applications using "static" classes and methods

or Alternative to running DB Lookup functoid through a loop?

Rachit

Varun,

If you are needing to setup cross reference data (key value lookups) you might load this data into the BizTalk management database. There are some xref tables you can load using BTSXRefImport.exe. The values on these tables can be retrieved/modified by using the Get Common Value/Get Common ID/Get Application Value/Get Application ID/Remove Application ID/Set Common ID functoids. You'll still have lookups happening though...

If you are interested in this approach check out the following:

http://msdn.microsoft.com/en-us/library/aa577624.aspx

http://geekswithblogs.net/michaelstephenson/archive/2006/12/24/101995.aspx

http://geekswithblogs.net/charliemott/archive/2010/03/14/biztalk-cross-reference-data-management-strategy.aspx