Best way of inserting data into SQL Server through BizTalk

Question

I have a requirement to process an excel attachment that comes via email, and then insert the data into a SQL Database. Need to be done through BizTalk.

What would be the best way to accomplish this, in terms of architecture/steps.

I have thought of a solution but it may not be the most optimum way of doing this.

My idea is to
1) retrieve the attachment via the pop3 adapter and save the attachment to the file system.
2) Parse it using a custom pipeline which creates a xml document of all the data
3) Debatch the xml into individual messages
4) Insert the data using sql adaptor.

Answer 1

I would do it almost the same way you explain. But i wouldnt save the attachment to the filesystem, but rather processed them directly. create a custom pipeline for splitting up the incoming excel-sheet into several messages which you loop through in the orchestration. (assuming that there are many messages in one excel-sheet...) -and in the inner loop you do an insert directly to sql using sql adapter