Process EDI 840 using Biztalk 2010

This demo intends to show how to configure trading partners, agreements and the creation of EDI Solutions in Biztalk  to process and exchange EDI messages to your partners. We will sends a Sales Order message in an X12 Electronic Data Interchange (EDI) format using the X12 840 schema. The message will be process by an Biztalk EDI  and will transformed the EDI message in to a SalesOrder schema (XML) and then  send the SalesOrder directly to an SQL Server.

The first thing of course is to create a new Visual Studio project:

·  Open Visual Studio 2010, on the File menu, point to New, and then click Project.

·  Under “Installed Templates”, select “Biztalk Project” template

And then select “Empty Biztalk Project” project type

Finally, give a name to you project: “Biztalk.Company.EDI840”

After we create our project: “Biztalk.Company.EDI840” we need to add the following files to our project:

 

• “ECommerceSalesOrder.xsd”

 

Xsd Code :

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://ECommerceSalesOrder.Inbound" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://ECommerceSalesOrder.Inbound" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="SalesOrder">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="CompanyCode" type="xs:string" />
        <xs:element name="PartID" type="xs:int" />
        <xs:element name="Quantity" type="xs:int" />
        <xs:element name="AskPrice" type="xs:decimal" />
        <xs:element name="RequestShipmentDate" type="xs:date" />
        <xs:element name="Address">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Line1" type="xs:string" />
              <xs:element name="Line2" type="xs:string" />
              <xs:element name="City" type="xs:string" />
              <xs:element name="State" type="xs:string" />
              <xs:element name="Country" type="xs:string" />
              <xs:element name="Zipcode" type="xs:int" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Contact">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Firstname" type="xs:string" />
              <xs:element name="Lastname" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Comments" type="xs:string" />
        <xs:element name="DateNow" type="xs:date" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Now we will add one  additional artifacts to our project:

• “X12_00401_840.xsd” that you can find in the “MicrosoftEdiXSDTemplates.zip” file available at FileLocation“C:\Program Files\Microsoft BizTalk Server 2010\XSD_Schema\EDI\MicrosoftEdiXSDTemplates”

Now create Map for Transform EDI Message to SalesOrder- EDI840ToECommerceSO.btm

Create SalesOrder Table in Sql server.

Script:

 GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[SalesOrder](

      [SalesOrderID] [int] IDENTITY(1,1) NOT NULL,

      [PartNum] [int] NOT NULL,

      [DateRequested] [datetime] NULL,

      [CompanyCode] [varchar](3) NOT NULL,

      [Qty] [int] NOT NULL,

      [UnitAskPrice] [float] NULL,

      [ShipDate] [datetime] NOT NULL,

      [SellToAddress] [varchar](255) NULL,

      [BillToAddress] [varchar](255) NOT NULL,

      [PartnerContact] [varchar](128) NULL,

      [CustomerComments] [varchar](500) NULL,

      [RFQStatuesId] [smallint] NULL,

 CONSTRAINT [PK_SalesOrder] PRIMARY KEY CLUSTERED

(

      [SalesOrderID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Generate the entire LOB Schema for Sales Table using WCF-SQL Adapter

• ·         Right click on project , click add generated items.
• ·         Click consume adapter service , select sqlbinding , configure server setting
• ·         Click on connect. Select Contract type client.
• ·         Select Tables-SalesTable
• ·         Select Insert Operation
• ·         Select Ok will generate Required LOB Schemas for SalesTable.

Now create Map for Transform SalesOrder  to Sql Insert Message- SalesOrderToSQLSalesOrderInsert.btm

Create Orchestration for process EDI 840 message , Transform and send to Sql server 

Now we are ready to build our solution and deploy to Biztalk Server!

Configure Application in Biztalk Admin Console.

Configure Party in Biztalk server.

Create two party UsParty and ThemParty.

Create Aggrement for both party.

Set Interchange Setting for ThemPartyàUsParty

Click on Idetifier.

Set   ISA05-ZZ , ISA06-THEM, ISA07-ZZ, ISA08-US

Set Acknowledgement setting , if want to send 997 to ThemParty

Similarly configure InterchangeSetting for UsPartyàThemParty

Click on Idetifier.

Set   ISA05-ZZ , ISA06-US, ISA07-ZZ, ISA08-THEM

Deploy and configure Biztalk Application

Add Reference of Biztalk EDI Application to your Application

Create Receive Location for Receive EDI840 edi file

Use EDIReceive Pipeline.

Create Send Port for Sql Server for insert data to sql table by importing autogenerated binding file.

Create Sendport for send 997 Ack , add filter

BTS.MessageType = “http://schemas.microsoft.com/Edi/X12#X12_997_Root”

Use EDISend Pipeline at AckSendPort