Using Edi in Biztalk, how to customize file processing rules and how to store edi data into SQL Server

As far as I know, Biztalk can receive EDI from trading partner, look up schema, validate the edi, generate ack, PROCESS EDI , convert edi to XML. These are what I have learnd from MSDN.Currently we don't implment biztalk and what we are doing is receive product information (spreadsheet/csv file) from trading partner, process the received file by using a processor we build, store all product information in SQL Server.

I have three questions:

1. Our received EDI contains products information, when biztalk processes edi, how and where can we apply our business rules/needs? E.g. we can calculate our selling price based on the promotion information contained in the edi file. Another example is we can modify the product description...etc, those sort of thing.

2. How to store edi data in SQL Server? Is that SQL Adapter? WCF-SQL? All product informaiton will be stored in one table and what are the key steps we need to do here?

3. After biztalk convert edi to xml, then what can biztalk do with the converted xml? Can Biztalk process the xml and map all data into our SQL DB schema?

Thanks in advance

I am on the run so i'll take a quick stab at these. Let me preface my answers by saying BizTalk is a very powerful engine, with endless possibilities and many ways to accomplish tasks.

1. This is dependent on what your logic involved. If you have multiple partners, you may do some pre-processing. Or you may choose to do it in the map if your logic is data dependent. It can also be done in post-processing by using custom pipelines.

2. There are a couple of possibilities for this. You can do this directly from the map, use WCF, SOAP, etc to send the XML, or do a specific map directly to SQL.

3. BizTalk works with XML natively, meaning every document is turned into an XML and all manipulation is done in XML. Yes, you could in theory create an xml schema of your DB and use a map to do so, or you could just CRUD directly from within a map.