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]

We’ve had a couple of projects recently with similar requirements to process an Excel file as a batch upload of data. One was a BizTalk project where the FarPoint Spread pipeline component was a good fit; the other was a Web app where we put together a custom parser based on the open-source ExcelDataReader. The custom solution was appropriate for the expected size of upload files, but wouldn’t scale well to deal with large files quickly, so I wanted to look at a distributed alternative using nServiceBus. My sample implementation is on MSDN Code Gallery here: nServiceBus Excel Upload. I’ll look at a comparative BizTalk solution in a future post.

If you haven’t come across nServiceBus, it’s a queue-based messaging framework which is inherently asynchronous. “Scalability and reliability are in its DNA”, and it has some impressive case studies. Using nServiceBus you can set up a simple publish-subscribe architecture between nodes, or a load-balanced architecture with a central distributor. In the distributed version, the upload sample looks like this:

(Note that the diagram represents the bus as a separate entity, in reality it’s distributed among the queues of all the nodes. The diagram also omits the distributor).

In nServiceBus, services are requested by publishing messages onto the bus. Requests are fulfilled by a handler which subscribes to a type of message. The Excel upload sample takes a workbook which contains a set of products and uploads them to the AdventureWorks database. There are three types of message:

  • StartBatchUpload – published when a file has been received and is ready to be processed; subscriber does some basic validation on the Excel data structure, and then for each row in the worksheet publishes an AddProduct message;
  • AddProduct – subscriber maps the product defined in the message to a stored procedure call which inserts the new product. When the last product in the batch is reached, sends a BatchStatusChanged message to the original publisher of the StartBatchUpload message;
  • BatchStatusChanged – logs the status change and renames the Excel upload file.

This is a basic example, more validation would be expected, but the workflow is representative. Parsing the Excel file is done quickly, allowing for any number of nodes to participate in the resource-intensive work of creating the products. Using a single host with 5 threads, an Excel file with 3,500 rows takes just over 4 minutes to process on a dev laptop. That’s 13 messages per second which is nothing special, but this is on a single host which is also running the distributor and SQL Server. The processing host has a flat memory profile (consistently around 40Mb) and runs at less than 20% CPU. The distributor takes around 15% CPU, and MSMQ another 15%.

For a much larger upload – 12,000 rows – the processing and memory profile is the same, and the upload takes around 14 minutes (~14 messages per second) on the same infrastructure.

Running the Sample

Access to a SQL Server instance with the AdventureWorks sample database installed is a pre-requisite. You’ll need to add the new stored procedure with uspInsertProduct.CREATE.sql. The connection string used by the host is specified in ExcelUpload.Host.exe.config (defaults to unnamed local instance).

You’ll need MSMQ running on all nodes. Queues are specified in configuration and are created by nServiceBus if they don’t exist – an exception is the storage queue for the distributor which needs to be manually created, this PowerShell snippet will do it:

      [Reflection.Assembly]::LoadWithPartialName("System.Messaging")

      [System.Messaging.MessageQueue]::Create(".\Private$\distributorStorage", $true)

Unzip the file ExcelUpload.Binaries.zip. You’ll have a batch file – start.cmd – and five subdirectories – Client, Host, Distributor, SampleFiles and Drops. Run start.cmd, check the console screens for errors, then copy one of the Excel files from SampleFiles to Drops. You should see activity in the host, client and distributor console screens, and new rows being added to the [Production].[Product] table.

If you drop the same file twice, the unique key on Products will be violated, so the upload will error. On a fresh install there are under 1000 products, so this resets the table to the default state:

delete [Production].[Product] where ProductID > 999;

Implementation Details

The sample uses the release version of nServiceBus – 1.9 – as the distributor was broken in the 2.0 beta at the time of writing.

The two console apps run the “client” (which monitors a configured file location for an Excel drop, and publishes the StartBatchUpload message), and the “host” (which subscribes to StartBatchUpload and publishes AddProduct and BatchStatusChanged messages). Both use Topshelf so they can run as a console, or can be installed as a Windows service (e.g. ExcelUpload.Client.exe /install).

If you want to run several hosts on the same machine, they will need to use different queues. Copy the whole of the Host directory, and modify ExcelUpload.Host.exe.config to specify a unique queue name:

InputQueue="ExcelUpload.Service.1.InputQueue"

Then run ExcelUpload.Host.exe from all the copied locations, and you’ll see the console hosts sharing the message processing when a file is dropped.

 

Posted on Wednesday, November 18, 2009 9:46 AM Code Gallery , nServiceBus | Back to top


Comments on this post: Processing an Excel upload with nServiceBus

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
I was just looking for something like this for one of my upcoming projects. That example that you have made is perfect for what i am planning to do.
Left by Nanny Cams on Jan 29, 2010 5:13 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
I was having the same problem when doing batch upload in MS Excel. I have looked everywhere for a possible solution, but found none. I'm glad I came across this article. I'm not familiar with nServiceBus Excel Upload and I'll definitely look into it.
Left by Toys on Feb 07, 2010 2:36 PM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
That's an amazing post. I really appreciate for this.It’s my first visit.I like very much your way of presentation.Thanks for making such a cool post which is very well written.Thanks again.Keep blogging.
Left by Bed and Breakfast Napoli on Feb 11, 2010 4:21 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
I am very happy with your post.Really very interesting post.Data Recovery Software|Guard Process
Left by david on Mar 04, 2010 3:07 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Hello. I'm new to the whole idea of SOA / messaging etc. and I can see how it lends itself to more loosely coupled, distributed, scalable systems. However, you mention that your custom solution "wouldn’t scale well to deal with large files quickly". However, 12000 records in 14 minutes seems excessively slow to me. Is this quick for using an ESB? Considering yours is a contrived example, should I expect worse performance in reality (although a production environment would be distributed rather than all on a development box)?

In the past, I have done similar tasks using a SQL database as a kind of message bus, but I never would have gotten away with this kind of performance. Although you do say that adding a product is "resource-intensive work", so I may be missing the point here due to the simplified nature of the example! Are we talking about a scenario whereby adding a product takes longer than we would like to have a database transaction open for?

Also, I don't see how this is robust, since if someone pulls the plug halfway through the 14 minutes, wouldn't the batch remains half-imported? I'm probably missing the point though. Thanks.
Left by Adam on Mar 10, 2010 9:47 PM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
;)
sohbet
Left by MuratCan on Mar 24, 2010 8:59 PM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Thanks for this post. I have been trying to learn how to do more things with nServiceBus. I find it very complicated. This helped me a lot.
Left by Cake Business on Mar 26, 2010 3:34 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
12,000 messages may be slow, but it's important to know when going for high-volume, scalable services, the time it takes to process is not as important as the fact that that time can remain constant as the demand for the system increases. It is possible to have multiple servers working a queue. Also, if the database goes down, all the messages are still available to run again. If you process a file directly to the database, and it goes down, you have no way to redo anything except delete what data was put in and start over.

I am doing something similar, but the amount of data for a single file is expected to go up as high as 800,000 records. If multiple users are adding files and the total number of records reaches the millions, it's good to know that I have NServiceBus helping me out.

I took a different approach since my files are going to be so large. The first thing I did was to chunk up the file into messages with about 10,000 records of raw data (this number is configurable). Once they are in the queue, I get complete fail over for them. So, I do have a couple extra steps - this is to answer some of the questions people are asking here :)

I also had another service which watches files on the FTP server which notifies the import process to download the file and process it. If you want that service, I'll give it to you. You would have to customize it a bit to work for you, I am sure.

I really like the diagram, it makes a clear picture of the process. I'm pretty lame at those . . .
Left by Chris Pritchard on Apr 10, 2010 4:35 PM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Excellent in all aspects. Sounds interesting. I think I’ll have to check it out and see what it’s all about.

Left by vacanze isola d'elba on May 12, 2010 7:50 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Very excellent post. I am beginning to see how Excel is used in more demanding applications. I am going to check this out in more detail when I have some time.
Left by Google First Page on May 17, 2010 8:23 PM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Really it's an awesome blog.I agree with most of what you are saying here.Thanks for the share!Waiting for your next upcoming post.
Left by Serre on Jun 01, 2010 7:32 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Pretty cool post. I just stumbled upon your blog and wanted to say that I have really liked reading your blog posts.Thanks a lot for sharing.Keep blogging.
Left by residence Lucca on Jun 03, 2010 2:18 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
awesome blog i agree with the all what you have

Top 10
Left by Jubail on Jun 07, 2010 8:25 PM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
I have been trying
Left by Financial on Jun 09, 2010 3:03 PM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
I am interested very much in the subject matter of your blog.Its really a very enjoyable blog.And i just like it.Thanks a lot.Keep blogging.
Left by Golf Course Advertising46 on Jun 14, 2010 3:27 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Unit Testing Silverlight and Windows Phone 7 Applications

Silverlight Performance on Windows Phone

Distributing and Monetizing Windows Phone Applications and Games

Development and Debugging Tools for Building XNA Games for Windows Phone
Left by online bingo bonus on Sep 07, 2010 5:39 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
The topic which you have discussed is very informative as well as valuable for all.Thanks a lot.I really appreciate it.Hope will post again soon.
Left by home automation systems764 on Sep 25, 2010 7:11 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Interesting post and thanks for sharing. Some things in here I have not thought about before.Thanks a lot.
Left by wealth magazine investor educati on Oct 11, 2010 2:30 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
A sample implementation of messaging using nServiceBus to process an Excel upload file. Uses distributed pub-sub for scalability, and ExcelDataReader from CodePlex for performance. Based on release 1.9 of nServiceBus.
Left by Stairlifts on Nov 09, 2010 8:12 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
This is a very good post.I need to test NServiceBus myself.And I think this is an excellent example for getting started with NServicebus.From my point of view should be contained in the official samples of the NServicebus package!
Left by best coffee maker on Dec 06, 2010 5:07 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
The resource like the one you mentioned here will be very useful to me! I will post a link to this page on my blog.
Left by paris visite on Dec 15, 2010 5:41 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Have found your web page. My pal mentioned it to me before, yet never got around to checking it out until now. I must express, I'm floored. I really enjoyed reading through your posts and will absolutely be back to get more.
Left by bmw air conditioning compressor on Dec 16, 2010 5:27 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
This is a really excellent read for me. Must admit that you are one of the coolest blogger I ever saw. Thanks for posting this useful article.
Left by eve online 60 day on Jan 12, 2011 7:29 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Spreadsheets created using Microsoft (MS) Excel are often considered to be an integral way of investigating how your company is progressing?
Left by Terrain a Vendre on Jan 18, 2011 5:34 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
What a wonderful idea. Art can not hide from people. It was created for the public!
Left by nike vr pro on Jan 18, 2011 3:08 PM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
thanks for the interesting post.
Left by infant crib mattress reviews on Feb 25, 2011 5:34 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
nice article .keep it up guys..
Left by best organic crib mattress on Feb 25, 2011 5:35 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
great post.
Left by standard crib mattress size on Feb 25, 2011 5:36 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Of course, what a great site and advisory posts, Can I add backlink - import your rss feed? Regards, Reader.
31 Day Fat Loss Cure Scam
Left by vdscvcv on Feb 25, 2011 7:17 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Hi this is Jenny, am from Florida. I am here to have some fun with mah friends
Left by 31 Day Fat Loss Cure Review on Feb 25, 2011 7:18 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Thanks a lot for discussing this information, I do not know about anyone else, but I can totally use it.
Left by 31 day fat loss cure on Feb 25, 2011 7:19 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
You know so much about this subject, made me think about it from so many different angles. Its like people arent interested unless it has something to do with Lady Gaga! Your stuffs great.
organic crib mattress reviews
crib mattress size
Left by defdsfd on Mar 02, 2011 6:47 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Google recently stopped development on Notebook, which means it's no longer open to sign-ups by new users or being improved. Directed by Nick Cassavetes. With Gena Rowlands, James Garner, Rachel McAdams, Ryan Gosling.Circus Ponies NoteBook is the award-winning Mac application for getting organized on OS X. De-clutter your Desktop, track your tasks, manage your projects,
Left by Notebook on Mar 19, 2011 9:33 PM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
I was looking for this information from long time and found your post. It will be very useful for me. Thanks and keep it up.
Left by 31 day fat loss cure review on Mar 23, 2011 7:14 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
thanks for the nice articles.
Left by 31 day fat loss cure scam on Mar 23, 2011 7:17 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Thanks for sharing!
Left by New York Yankees hats on Mar 25, 2011 4:21 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Wonderful solutions.Id like to suggest taking a look at things like sausages. What are your thoughts?
Left by how to hack the stock market on Apr 12, 2011 10:03 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
This is a appealing post by the way. I am going to go ahead and bookmark this post for my sister to read later on tonight. Keep up the fine work.
Left by no nonsense muscle building on Apr 12, 2011 10:05 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
These are so advanced tricks...
Left by unfaithful partner on Apr 19, 2011 12:04 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Great tricks, I will surely make use of them
Left by search court records on Apr 24, 2011 11:56 PM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
we will come back to see if you have any follow up to this ;-) Indian escorts London
Left by London Escorts on Jun 05, 2011 12:43 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...

You are fantastic as your work and I really think that this is a good topic to share send flower uk
Left by nabiha hayat on Jun 08, 2011 9:29 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
No doubt The information presented is quite useful. By using this I think all can prevent major breakdown
Left by ceh on Jun 11, 2011 5:39 PM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Fantastic precise info and this is one of the most nice blogs Ive read in a very long time ceh | xbox live codes


Left by jam on Jun 11, 2011 5:41 PM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Thanks a lot for sharing all the information with us
Thanks, thanks, thanks..
Left by freetress on Jun 18, 2011 9:37 AM

# CuteAnjeli
Requesting Gravatar...
It is very interesting for me to read that article. Thank author for it. I like such topics and everything connected to this matter. I definitely want to read more soon. send flowers to lebanon || flowers to ireland
Left by send flowers to lebanon on Jun 28, 2011 11:43 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
NServiceBus is a powerful, yet lightweight, open source messaging framework for designing distributed .NET enterprise systems. It gives programmers a head-start on developing robust, scalable, and manta.
Left by Heathrow Taxi on Jul 25, 2011 7:12 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
NServiceBus is an open source enterprise service bus implementation in .NET. The core features of NServiceBus are the messaging capabilities. It also supports a long-running message exchange pattern called a saga. When it comes to saga support, NServiceBus comes with a Saga base class, templated by the saga data type. Saga data type contains the data about the state of the long-running message exchange orchestration process.
Left by Rack-Mount Monitor on Aug 03, 2011 7:45 AM

# beststyle
Requesting Gravatar...
I agree that it's not very relevant,but isn't the point of it to show what people are currently saying about a topic.
Left by alinajiwarm on Dec 08, 2012 1:54 PM

# only fashion
Requesting Gravatar...
the website is pleasing me in the right way. I hope new posts will come soon. http://onlyfashiontalk.com/
Left by animoon on Jan 09, 2013 5:06 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
It's so motivating along with encouraging personally plus your people to see the tips you have got. Karen Millen Outlet UK

Left by matthew woods on May 16, 2013 7:50 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...
Well, Its been nice to see people are developing nice to batch processing excel upload like we did in other upload component.
It Helps lots
Malik Khan
Left by Malik Khan on Apr 11, 2014 9:55 AM

# re: Processing an Excel upload with nServiceBus
Requesting Gravatar...

Well, Its been nice to see people are developing nice to batch processing excel upload like we did in other upload component.
It Helps lots
Malik
Left by Malik Khan on Apr 11, 2014 9:58 AM

Your comment:
 (will show your gravatar)


Copyright © Elton Stoneman | Powered by: GeeksWithBlogs.net