Sending infopath forms via email (as attachment) to be parsed by SQL Server 2005?

Question

Just looking at the requirements of a new project and I wanted to make sure this use case was sound:

• user fills in InfoPath (2003) form locally on their PC
• a button within the InfoPath form titled 'submit' brings up a new outlook (2003) email message with the infopath form attached. User presses sends and email is sent to an exchange mailbox.
• sql server preiodically checks this mailbox, downloading any new submissions with the infopath form attached
• sql server parses the attachment and the fields within the infopath form.

Is SQL Server capable of parsing mail attachments this way? Any caveats with this approach?

The attraction to using Outlook as the submission technology is that the process for the user is the same if they are offline. Outlook will then automatically sync when they come back online. It is essential that users have some way to fill the forms in offline, 'submit' them, and then have then synced automatically with the server when they next come online.

edit: to clarify, I am not looking for a way to cache form data from the server->client. I am looking to cache the completed form. Building a separate application to cache the completed reports on the client is not an option.

Answer 1

Later versions of SQL Server are capable of running .NET code within them, and as such you might be able to poll a mailbox from SQL Server and process an InfoPath form. However, I'm not sure I'd do it this way.

It might be better to consider writing a Windows Service that does this work. The Windows Service would start up, inspect the mail box of a "service account", read the mails, extract the attachments, process the xml and, eventually, write the data to SQL. It could also, presumably, respond to that mail with a confirmation or errors if business rules or validation errors occurred.

I'm not sure I'd put all of the above logic into SQL - for one thing, I suspect you'd have issues with accounts (having to have the account SQL was running under be able to access the Exchange mailbox account).

Your mileage may vary, and you should prototype this to determine what works best for you, but I'd try and keep the code the uses Exchange as a "work queue" separate from SQL and only put the code that deals with writing data into tables in SQL.

Answer 2

I would not use the approach you outlined above. There are several approaches that appear to me to be more desirable than having SQL Server looking at an Exchange Mailbox. The major point that you make and an important requirement is that the InfoPath form be allowed to work in offline mode. I would think of the "offline mode" and the "data transfer" parts of your project as two distinct and separate pieces: 1) The form and the data should be stored on the client until a connection to the Internet is available and 2) once the connection is available the form and data should be transferred to the server.

You can setup your InfoPath form to submit directly to the SQL Server and bypass the Exchange "middleman" entirely. The setup in InfoPath when you are designing your form is pretty straight forward: 1) you enable "Submit data" for the connection and 2) you configure the submit options. This article has the details about how to do that. Furthermore, your connection to the SQL Server may be setup for offline use, as it's discussed in this article. The only caveat with this approach is that you may need to change your database schema to support it.

Another approach is to have your InfoPath form submit to a SQL Server 2005 HTTP Endpoint. The InfoPath client is just a glorified XML Editor and the HTTP Endpoint is basically a different name for a web service. You receive the form data at the HTTP endpoint into a staging table where the data is stored as XML and then you can do your parsing of that data from that staging area. Still you will have to setup the InfoPath connection for offline use. The major caveat with this approach is that Microsoft will deprecate HTTP Endpoint in SQL Server 2008 in favor of WCF.

And the other approach I would like to suggest is to use WCF itself to receive the XML form data from the InfoPath client. This approach would require you to connect the form's data source to you WCF web service at design time and then also setting up the form for offline use.

I hope that this will be useful to you and at the very least point you in the right direction.

Answer 3

I've seen similar projects that resorted to an Express edition on the client, save the infopath (or app data) in Express and use Service Broker to deliver to center, because of guaranteed delivery semantics of SSB vs. mail. This gives you an all SQL solution easier to sell to IT and you don't need polling on the server. Also you will not have to deal with MIME parsing, is all straight forward XML processing. It is not for the faint of heart though, getting SSB up and running is a challenge. If you decide to go with mail delivery, an external service will be arguably easier to build, debug and troubleshoot. There are some finer point issues you should have an answer for: -How will you keep consistent the mail dequeue operations and the table write operations? Your component must engage the Exchange read/delete and the SQL insert into one distributed transaction. - Is your logic prepared to deal with infopath docs coming out of order? mail transport makes absolutely no guarantee about the order of delivery, so you may see an 'order delete' doc before the 'order create' doc - How are you going to detect missing documents (not delivered by mail)? Are you going to implement a sender sequence number and end up reinventing TCP on top of mail? - Does your processing allow for parallel process of correlated documents? If thread 1 picks up doc 1 and thread 2 picks up doc 2 from same sender and doc 2 is correlated with doc 1 (ie. refer to same business transaction), what will happen at the database write? Will it deadlock, will it loose an update, will one be rolled back?

There are many dragons under the bridge ahead...