- This topic has 4 replies, 1 voice, and was last updated 9 years, 7 months ago by
.
-
Posts
-
-
Hi,
I have created a map that processes our Point of Sale transactions. In this map I need to do a lookup for each tender(over a hundred for each file). I am using the DBLookUp fuctoid. Every now and then I get empty fields as the DBLookUp must fail upon connection. I do not want to fail the file as it would cause sales not to be posted in the overnight batch run. What I would like to do is loop a couple of times if the DB connection fails. I’m thinking that I should call out to a external assembly that retries the connection/query before returning empty fields.
Does anyone have an example of a External assembly that connects to a SQL 2000 databases?(Looking for the best approach to my problem above.
Thank you and regards
Rob
-
Have you considered using the SQL Adapter to perform the query and aggregating the results with with original message in a map.
You will get the in-built retry capability of a send port and no external class library to build and deploy.
-
Hi Greg,
the files I’m processing can have up to 15,000 rows. We can have up to 1000 Sales transactions in one file. These Transactions can come in many forms and the rows come in different combinations. Depending on the sequence of the rows and their tags. I map the file accordingly. Each transactions can have multiple tenders(Which I do a lookup against them and get the right codes for our ERP system). We have over 150 stores hence 150 files being processed simultaneously.
I can’t see how I could manage that via a SQL adapter. I was thinking more along the lines of having a scripting Functoid call to a External Assembly that does the Database Lookup. that way I could have a few retries in the code. At the end of the day. It would be preferred by the business that I have one corrupt transaction then a whole file not being processed. If there is a way where I do one LookUp at the beginning of my processing and then I’m able to keep it memory for my tender Look Up it might be a solution, but i could not afford to have the whole file fail due to that Look Up failing.
Currenlty the problem occurs probably less than .01% of the times, but enough for finance to log it as a problem. the entire process is very quick and accurate. Apart form the .01%.
If you still believe the SQL adapter approach is best. Could you please give me a little more detail as to how I would do it.
Thanks
Rob
-
How many codes do you have and how often do they change?
One possibility is to provide a custom data mapper assembly, which extracts the codes from the database and caches them in memory.The codes will always be available in cache, no more expensive database lookups. You can have a cache load/refresh mechanism that would retry if the database is unavaliable. The cache refresh mechanism may just be restarting the Biztalk host, if the rate of change of the codes is very low. Check out the Enterprise Library CacheManager http://msdn.microsoft.com/en-us/library/ms978498.aspx
The assembly would have a static function:
public static string GetERPCode(string tenderCode)This can be referenced inside the map using the scripting functoid/External Assembly.
This would improve performance and remove the occasional database connectivity issue.-
Thanks Greg,
sounds exactly what I need to be doing. I have a DTS package that updates the Lookup tables each night. There are minimul changes and reason for having the LookUps was to have no user interaction for any updates. By having a nightly process that updates the cache would be efficiant.
Thanks again.
Rob
-
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.