Geeks With Blogs

News This is the *old* blog. The new one is at blog.sixeyed.com

Elton Stoneman
This is the *old* blog. The new one is at blog.sixeyed.com

 

[Source: http://geekswithblogs.net/EltonStoneman]

Following on from the sample for processing Excel uploads with nServiceBus, I have a comparable version using BizTalk on MSDN Code Gallery here: BizTalk and nServiceBus Excel Upload. The BizTalk (2006 R2) sample uses a FILE receive port with a simple pipeline component to disassemble the Excel file into separate messages. A SQL send port subscribes to the messages, and with an outbound map calls the AdventureWorks stored procedure to insert each product.

Processing Excel in BizTalk is nothing new, but I wanted to do a comparison against the nServiceBus example, and also see how BizTalk and nServiceBus could be integrated. My original thinking was that the trigger in the nServiceBus solution relies on a FileSystemWatcher, which is less reliable and less flexible than BizTalk’s FILE adapter. A hybrid solution could use the FILE adapter to receive and parse the upload, then send each row as an AddProduct message to MSMQ, which the nServiceBus handler subscribes to.

Running this on the same environment (a Windows Server 2003 VM running under VirtualBox in Ubuntu 9.04), the BizTalk solution processes the 3,500 row Excel file in 3 mins 10 seconds (compared to 4m 15s for the distributed NSB running with 5 threads), and the 12,000 row file in 11m 14s (compared to 14m 0s). I was surprised to find the BizTalk solution running more quickly, as the original NSB was using non-recoverable messaging, so all messages were in memory, while BizTalk had the latency of writing to the message box.

Performance Comparison

This is not an intended to be a thorough benchmark of NSB and BizTalk – both sample projects are basic, untuned implementations, and the tests are on a single box rather than two or three. But for comparison, I ran the 3,500 row upload repeatedly under different configurations:

1.       NSB with distributor – host and distributor using 20 threads

2.       NSB with distributor – host and distributor using 20 threads, recoverable messaging

3.       NSB without distributor – host using 20 threads

4.       NSB without distributor – host using 20 threads, recoverable messaging

5.       BizTalk FILE receive and SQL send

6.       NSB parsing Excel file, BizTalk subscribing to AddProduct messages – MSMQ receive using batches of 100

7.       BizTalk parsing Excel file, NSB without distributor subscribing to AddProduct messages – host using 20 threads

Which gave these averaged results:

Boosting the number of threads dramatically improved the NSB performance, and removing the distributor halved the duration. I would expect that running the distributor on a separate node would yield similarly good results on each processing node. The hybrid BizTalk/NSB configurations were the slowest – to be expected, as you have the latency of the message box and the latency of MSMQ saving to disk. NSB configurations without recoverable messaging were only marginally slower than the recoverable version, which seems incorrect based on Udi Dahan’s performance benchmark, so I’ll need to look into that further.

There’s plenty of scope for improving performance in both solutions. BizTalk can be endlessly tuned (the 2004 guidelines still apply as a good starting point). By modifying the NSB solution to send the AddProduct messages in one opration and using 80 threads for the handler, duration fell to 25 seconds – 140 messages per second.

Integrating BizTalk and nServiceBus

Despite having the worst performance, integrating BizTalk and nServiceBus is a viable option which may be very useful in some cases. It would allow you to leverage BizTalk’s adapter suite and mapping functionality to join LOB systems into an nServiceBus estate. Integration is actually very straightforward. By default NSB uses MSMQ, so to publish messages from BizTalk to an NSB subscriber just means configuring an MSMQ send port with the expected queue, and mapping your message to NSB format – which is an envelope containing one or messages:

<?xml version="1.0" ?>

<Messages xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.net/ExcelUpload.Messages">

  <AddProduct>

    <BatchId>7a80fed4-9d30-44fe-95b0-08218c5f328e</BatchId>

    <RegistrationIndex>58</RegistrationIndex>

    <RegistrationsInBatch>254</RegistrationsInBatch>

    <BatchSourcePath>E:\ExcelUpload\1.0.0.0\Binaries\Drops\ProductUpload.xls</BatchSourcePath>

    <OriginatorDestination>ExcelUpload.Client.InputQueue@WIN2003R2-VM</OriginatorDestination>

    <Name>new product 58</Name>

    <ProductNumber>xlu-np-58</ProductNumber>

    <SafetyStockLevel>100</SafetyStockLevel>

    <ReorderPoint>20</ReorderPoint>

    <StandardCost>10</StandardCost>

    <ListPrice>15.5</ListPrice>

    <DaysToManufacture>60</DaysToManufacture>

    <SellStartDate>2005-02-27T00:00:00.0000000</SellStartDate>

  </AddProduct>

</Messages>

The MSMQ adapter lets you specify Recoverable and Transactional flags, so your BizTalk-generated messages have the same durability options. When an NSB handler is listening at the queue, it processes the BizTalk messages in the same way as NSB messages.

Publishing NSB messages to BizTalk is trickier, as the Send and Publish methods from NSB check to see if there are any subscribers before they write to the queue (compare this to the Notify method in RhinoServiceBus). If a BizTalk MSMQ receive port is the only handler, no subscribers will be registered with NSB and the message won’t be published. So you need to either call Send with a named queue (Bus.Send<AddProduct>("ExcelUpload.AddProductService.1.InputQueue", m =>...), or send a subscription message from BizTalk to register a subscriber. In the the second option you’d need to specify the message type in the subscription:

<?xml version="1.0" ?>

<string>ExcelUpload.Messages.StartBatchUpload, ExcelUpload.Messages, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null</string>

 

Running the Sample

(If you haven’t seen the original NSB sample, have a read as it details the pre-requisites).

To run the BizTalk solution, download ExcelUpload.BizTalk.Binaries.zip, unzip it and:

·         Copy ExcelUpload.PipelineComponents.dll to your pipeline components directory – e.g. C:\Program Files\Microsoft BizTalk Server 2006\Pipeline Components

·         If you haven’t done so from the previous sample, run uspInsertProduct.CREATE.sql

·         Run uspInsertProduct.CREATESYNONYM.sql

·         Import the MSI into BizTalk

·         Run the MSI

·         Start send port SendInsertProduct.SQL (you’ll need to change the connection details)

·         Enable receive location ReceiveStartUpload.FILE.XLS (monitors c:\drops\ExcelUpload)

·         Drop an Excel file

As before, you’ll need to clear down the database between runs if you drop the same file repeatedly.

If you want to run the hybrid solutions, you should use the binaries in the new download rather than the original, as these run without a distributor. Run start.cmd and there are three windows – Client (the file watcher), Host (the StartBatchUpload handler) and AddProductService (this is the AddProduct handler).

To run configuration 6 – NSB parsing the file and BizTalk handling the AddProduct messages:

·         Run start.cmd

·         Kill the AddProductService console

·         Disable receive location ReceiveStartUpload.FILE.XLS

·         Enable receive location ReceiveAddProduct.MSMQ

·         Unenlist send port SendAddProduct.MSMQ

·         Start send port SendInsertProduct.SQL

·         Drop an Excel file

To run configuration 7 – BizTalk parsing the file and NSB handling the AddProduct messages:

·         Run start.cmd

·         Kill the Host and Client consoles

·         Enable receive location ReceiveStartUpload.FILE.XLS

·         Disable receive location ReceiveAddProduct.MSMQ

·         Start send port SendAddProduct.MSMQ

·         Unenlist send port SendInsertProduct.SQL

·         Drop an Excel file

Source Code

The source and referenced assemblies are in ExcelUpload.BizTalk.Source.zip. There is a VS 2008 solution for the nServiceBus components, and a VS 2005 solution for the BizTalk components.

Posted on Saturday, November 21, 2009 1:57 AM BizTalk 2006 R2 , Code Gallery , nServiceBus | Back to top


Comments on this post: Processing Excel uploads with BizTalk and nServiceBus

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
To run configuration 7 – BizTalk parsing the file and NSB handling the AddProduct messagesData Recovery Software|Guard Process
Left by Robin on Mar 04, 2010 3:21 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
To run configuration 6 – NSB parsing the file and BizTalk handling the AddProduct messages:Restore Deleted Files|进程保护
Left by hadisk on Mar 04, 2010 3:22 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
This was a very well-written and enjoyable post to read.This is exactly what I've been looking for.Thanks a lot for sharing.Keep up the good works.
Left by vacanze Follonica on Mar 16, 2010 10:11 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Thanks for this informative post. I have a question though. Can I use Excel for business things. Will it make me look professional?
Left by start a cake business today on Mar 26, 2010 3:48 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Great post. This one is the blog which I like most.I would like to thanks that master brain who make all this for the readers like me.keep up writing good.Thanks.Keep it up.Keep blogging.
Left by cctv systems on Apr 07, 2010 4:23 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Nice post having excellent contents.I agree with most of what you are saying here.Your tips are extremely valuable.Thanks a lot for sharing.Keep blogging.
Left by ecommerce252 on Apr 12, 2010 2:41 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Great post.Really an innovative and interesting idea. I really like the tips you have given.I am interested very much in the subject matter of your blog.Thanks.Keep blogging.
Left by case vacanza olbia on Apr 14, 2010 3:41 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Thanks a lot for sharing all the information with us
Thanks, thanks, thanks...
Left by Registro de Dominios on May 02, 2010 3:29 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Thank you for the information on the Excel uploads. this has has me understand the process a lot better.
Left by Master Planned Communities on May 14, 2010 3:45 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Thank you for posting this. I am new to Excel and this helps tremendously.
Left by Free Hip Hop Beats on May 17, 2010 8:19 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Thanks for sharing your valuable informations about Excel uploads. Excellent write-up on providing value to readers before asking for anything.
Left by noleggio matrimoni roma on May 31, 2010 8:44 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Excel is very complicated. I get most of what you are teaching bu I am still confused. I think I need to practice and read some instructions.
Left by Rancho Santa Fe Real Estate on Jun 08, 2010 12:27 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
I am following your blog regularly and got great information.I really like the tips you have given..Thanks a lot.Keep up the good works.
Left by disk backup solutions-45 on Jun 10, 2010 4:30 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
This is one of the creative post and it’s very innovative one..Each & every tips of your post are awesome.I wanted to thank you for this excellent read!! I definitely loved every little bit of it.Keep it up.
Left by home security systems47 on Jun 12, 2010 5:16 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
This is a great article. Thank you for the wonderful information.
Left by Couples Therapist Upland on Jun 21, 2010 4:30 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
I really appreciate the tips that you give in your blog. Thanks again for the awesomeness.
Left by Couples Counseling Upland on Jun 22, 2010 9:54 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
It is good to see posts that give truly quality information.I like the way you describe all the things and the examples.Thanks a lot.Keep blogging.
Left by dedicated hosting443 on Jul 07, 2010 7:41 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Wonderful article, thanks for putting this together! This is obviously one great post. Thanks for the valuable information and insights you have so provided here.
Left by Husky 1750 power washer on Jul 22, 2010 8:35 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Very interesting topic will bookmark your site to check if you write more about in the future.Thanks.Keep blogging.
Left by personal care products.73 on Jul 28, 2010 7:20 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Excellent post.I want to thank you for this informative read, I really appreciate sharing this great post. Keep up your work…
Left by affitto case Venezia on Oct 01, 2010 1:59 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
That is an excellent post. I accidently stumbled across your blog and found it to be very interesting.I have been reading a lot on here and have picked up some great ideas.Keep blogging.
Left by Agritourism Tuscany on Oct 02, 2010 2:23 AM

# mlm home based business
Requesting Gravatar...
Very interesting and informative article to be read...
Left by Pulkit Agrawal on Oct 28, 2010 10:36 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
I learned a lot of new thing about BizTalk and NserviceBus from this worthy post..BizTalk is a fine centralized message broker with many adapters to 3rd party applications, but service buses are inherently distributed not centralized. NServiceBus guides developers away from these dangerous anti-patterns while still providing the needed messaging patterns and integration.
Left by Uffizi tickets reservation on Nov 11, 2010 5:03 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
This is useful blog I have been here reading for about an hour. I am a newbie and your success is very much an inspiration for me.
Left by Residence apulien on Dec 24, 2010 6:41 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
I found lots of interesting information here. The post was professionally written and I feel like the author has extensive knowledge in the subject. Keep it that way.
Left by Kart Racing on Feb 21, 2011 11:56 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Resources like the one you mentioned here will be very useful to me! I will post a link to this page on my blog. I am sure my visitors will find that very useful.
Left by registry easy review on Feb 22, 2011 12:40 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
This is an important blog . Great job
Left by Credit Card In Australia on Mar 01, 2011 11:52 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Wow nice information you have shared here. Actually Google made searching of information easy on any topic. Well keep it up and post more interesting blogs..thanks a lot !!!
Left by orthotics on Mar 04, 2011 12:03 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
What I dont understand is how you are not even more popular than you are now. You are just so intelligent. You know so much about this subject, made me think about it from so many different angles. Your stuffs great. Keep it up..
Left by citomel on Mar 08, 2011 6:38 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
I like your blog post, keep up the great content. I am always looking for a good piece of writing like this. Thanks
Left by CCTV Camera on Mar 16, 2011 2:26 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Many thank you for taking the time to publish this information very useful..We will still waiting for some interesting thoughts from your side in your next post. This certainly got me thinking about this issue, thanks dear...great work
Left by electric grill on Mar 17, 2011 3:37 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Very informative article. You have both insight as well as courage to say the right thing in a proper manner. You may call it a coincidence but we also thought about the same thing.
Left by Phentramin-D on Mar 21, 2011 12:28 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which we all need, thanks for all the enthusiasm to offer such helpful information here!!
Left by Cincinnati Carpet Repair on Mar 27, 2011 8:39 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
This is really an outstanding and distinct article which I have never seen any where. I want to visit your site more and more times so I have bookmarked your site.thanks
Left by standard crib mattress size on Jun 03, 2011 6:53 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
I came to your article from another article and am really interested in this learning about this.Thanks for sharing.
Left by organic crib mattress on Jun 03, 2011 6:54 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
you have done a good job here giving us a whole new way of looking at certain issues, great topic ;-) London Indian escorts </a
Left by London Escorts on Jun 05, 2011 12:45 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
After I read your article, I was quite interested in what you write because it is very useful for me. please keep it up
Left by freetress on Jun 18, 2011 9:39 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Thank you for posting this. I am new to Excel and this helps tremendously.
Left by Hip Hop Beats on Jun 21, 2011 11:58 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which we all need, thanks for all the enthusiasm to offer such helpful information here!!
Left by how to meditate for beginners on Jul 14, 2011 6:53 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Dude!!! I am not much into reading, but somehow I got to read lots of articles on your blog. It’s amazing how interesting it is for me to visit you very often.
Left by personal loans on Aug 28, 2011 7:22 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Thanks for uploading this topic. Will be of great use for me, thanks again :-)
Left by Austin Computer Repair on Nov 16, 2011 1:30 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Great post ,well-written and understandable, easy to read. I’m I little wiser now..
Left by Roofer Sheffield on Nov 19, 2011 11:57 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
God job.As far it is great post about Excel upload.Keep it up
Left by Nue Science on Dec 06, 2011 7:46 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Good work.You wrote kinda little long .But it's ok when this kind of subject comes.Thanks for post.
Left by Douglasville bankruptcy lawyer on Dec 10, 2011 4:45 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
What more special post ?
Left by Baby Bullet on Dec 13, 2011 8:31 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
configurations shows correctly . thanks for Those useful information . Zquiet
Left by Muscle Warfare on Dec 25, 2011 10:10 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Thank you very much for showing the configuration properly.
Left by incontinence products on Jan 12, 2012 6:39 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
this is awesome sharing with to much information.i have to say this is impressing work.Excel processing with biztalk is really nice concept.
Left by san antonio dental on Mar 01, 2012 11:27 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
I will definitely visit this site again because I learned a lot and got very helpful information from your blog. Thanks for sharing.This was a stimulating read and I thoroughly enjoyed what you said.
Left by online rsa Queensland on Apr 11, 2012 6:27 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
This is a nice piece of coding you have put together here. I am not an expert on web coding but never the less this is a great blog.
Left by Security Info on Jul 01, 2012 10:54 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Great coding you've created. I was just surfing your blog and found this post amusing.
Left by san diego beach rentals on Aug 12, 2012 11:24 PM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Thanks for providing this useful stuff.Just stumbled on your blog and got interesting material here.
Left by CCTV Camera on Feb 20, 2013 4:44 AM

# re: Processing Excel uploads with BizTalk and nServiceBus
Requesting Gravatar...
Great! This content is innovative, there are a lot of new concept,it gives me creativity.I think I will also motivated by you and think about more thoughts. fiberglass cloth
Left by zemkarlos on Aug 25, 2013 6:07 PM

Your comment:
 (will show your gravatar)


Copyright © Elton Stoneman | Powered by: GeeksWithBlogs.net