Export (0) Print
Expand All

Legacy File Integration Using Microsoft BizTalk Server 2000

Microsoft Corporation

February 2001

Summary: This article discusses the integration of existing legacy systems using Microsoft BizTalk Server 2000. It is intended as an introduction to a range of concepts associated with BizTalk Server 2000 and is suitable for both technical and non-technical audiences who might be working with BizTalk Server for the first time.

Specifically, this paper provides an overview of the BizTalk Server tools that enable you to create the required components to interface to legacy systems. This includes a scenario with three phases that demonstrates integration by using industry-standard XML and EDI file formats as well as delimited and positional files. (28 printed pages)

Download BTSLegacyFile.exe.

Contents

Introduction
Overview
Integrating Your Legacy System with the World BizTalk Editor: What Is It, How Does It Work, What Is the Purpose? How to Use Existing Data Sources Scenario Overview Internal Application Integration Integrating Customers with EDI and Flat Files Integrating Suppliers with Flat Files Appendix: Sample Installation

Introduction

A member of the Microsoft® .NET Enterprise Server family of products, Microsoft BizTalk™ Server 2000 unites, in a single product, enterprise application integration (EAI) and business-to-business integration. BizTalk Server 2000 enables developers, IT professionals, and business analysts to easily build dynamic business processes that span applications, platforms, and businesses over the Internet.

In addition to BizTalk Server 2000, Microsoft, with its industry partners, has led innovation on enabling technologies that are necessary for Internet-based business solutions, including BizTalk Framework 2.0, which is a platform-independent, Extensible Markup Language (XML) framework for application integration and electronic commerce. BizTalk Framework 2.0 provides the basis for interoperable reliable messaging for BizTalk Server 2000.

As well as these innovations, Microsoft recognizes there are many legacy systems in enterprises today that do not currently support XML. To provide a complete solution framework, BizTalk Server 2000 integrates with a wide range of file formats. This paper discusses the integration of existing legacy systems within EAI and business-to-business frameworks that use BizTalk Server 2000. It is intended as an introduction to a range of concepts associated with BizTalk Server 2000 and is useful to technical and non-technical audiences that might be working with BizTalk Server for the first time.

Specifically, this paper overviews the BizTalk Server concepts that provide you with the ability to create the required components to interface to legacy systems. This includes a scenario with three phases that demonstrates integration by using industry-standard XML and EDI file formats as well as delimited and positional files.

Overview

It is generally acknowledged that organizations must embrace the global marketplace in order to expand the market for their goods and services. While embracing the global market expands the possible reach of an organization, it also introduces fundamentally new computing demands. It requires systems that run 24 hours a day, 365 days a year.

In addition to maintaining high availability, organizations need to reduce cycle times for every aspect of these systems. This enables them to reduce the time to market and achieve a higher level of service for both customers and partners.

In order for organizations to be able to act, react, and adapt at the speed that the modern economy requires, they need to be able to make decisions faster than they ever have before. They need to transform data into information and information into knowledge. They need to be able to share knowledge more effectively to assist in the identification of new business opportunities and to capitalize on opportunities when they are available.

Central to all of these changes is integration and interoperability between systems. The computing platform should enable you to use industry standards as well as organizational systems and skills to achieve the greatest gains both in time-to-market and efficiencies.

In order to minimize time-to-market, it is critical that organizations are able to use existing systems to reduce the time-to-market, regardless of the operating environment, programming model, or other constraints. In addition, to have a single, consistent infrastructure, the integration must enable organizations to integrate applications and processes internally as well as extend the integration to customers and business partners.

Integrating Your Legacy System with the World

Most of the existing or legacy systems that are currently active in the corporate world are proprietary and utilize proprietary interfaces to expose themselves to the outside world. These take the form of either a document import facility or the exposure of an underlying data model through an application programming interface (API) or API equivalent. As a result, the integration of internal or external applications is an expensive exercise both in terms of initial development and ongoing maintenance.

Microsoft BizTalk Server provides the ability to expose these applications through an XML interface; therefore supporting both proprietary data formats as well as current and potentially future standards in XML.

Why is this important? BizTalk Server allows you to reuse existing files to take advantage of the investment you have made in current data formats that meet the specific needs of your business.

BizTalk Editor: What Is It, How Does It Work, What Is the Purpose?

BizTalk Server defines the Extensible Markup Language (XML) representation of a document by using a graphical, simple-to-use tool. This tool, BizTalk Editor, enables you to create, edit, and manage specifications. A specification is a BizTalk Server-specific XML schema. It makes extensive use of XML, providing a common vocabulary to handle overlaps between syntactic, database, and conceptual data schemas.

Specifications represent the structured data as XML, regardless of the original format, and are a core component of integrating existing and legacy file formats into BizTalk Server.

Each specification describes the structure of the file, given a specific set of tags. BizTalk Editor also provides several templates that can be used as starting points for creating specifications for common documents, such as purchase orders, invoices, and advance shipping notices.

To assist in integrating formats, such as various flat files, BizTalk Editor enables the specification of the basic parsing rules for interpreting or producing file formats other than XML.

How to Use Existing Data Sources

With the range of legacy systems currently available and the industry tendency to redefine terms on a regular basis, the following explains the basic terms and concepts used in the sample scenarios. Specifically, this section explains positional and delimited files, as well as the function of parsers, serializers, receive functions, and envelopes.

What Are Positional and Delimited Files?

A delimited flat file contains one or more records separated by a record delimiter. A delimited record contains one or more fields separated by a common field delimiter. The following are examples of delimited files with delimited records.

Example 1

DATE,PRODUCT,PRICE
DATE,PRODUCT,PRICE

Example 2

DATE,PRODUCT,PRICE* DATE,PRODUCT,PRICE*

In Example 1, the delimiters are a comma (,) for separating the fields and a carriage return (<CR>) for distinguishing the end of records. In Example 2, the delimiters are a comma (,) for separating the fields and an asterisk (*) for distinguishing the end of records.

When creating specifications within BizTalk Editor, remember that BizTalk Server does not read delimiters as part of the data when processing the files.

A delimited file can have positional records within it. A positional flat file consists of fields that are the same length and records that have a common end-of-record terminator.

The following is an example of a positional format:

DATEXXPRODUCTPRICEX*000110A123   $15.60*

The date field is fixed at a length of six characters, while the product code and price are fixed at a length of seven and six characters, respectively. In this example the end-of-record marker or delimiter is an asterisk (*).

When using BizTalk Server, it is important to note that a positional record must always be a child of a delimited record. The delimiter character specified for the parent-delimited record must not appear in the data of the child positional record.

It is worth noting that files can consist of a combination of delimited and positional records.

Envelopes

A BizTalk Server envelope consists of two key pieces of information:

  • The type of envelope, which should match the envelope format for flat file, custom XML, X12, EDIFACT, or reliable document types.
  • The optional envelope specification.

Envelopes fall into two main categories or types:

  • Input document envelopes are required if the input document type is flat file because the envelope contains information about how to parse the document into XML and which parser should process the document. For additional information, see the previous section on flat files.
  • Output document envelopes are used to wrap an output document that has been transformed into the native format. The envelope used with an output document is specified in the port configuration. An envelope is always required if the output document is in an X12 or EDIFACT format. For documents that are output in a custom XML format, the reference specification is used to assist with the serializing process.

Envelopes are used in conjunction with parsers (input) to take input files into XML or serializers (output) to take XML intermediate files into the output format.

Parsers and Serializers

Parsers and serializers are the key tools for facilitating integration into legacy file formats. Parsers are a component of BizTalk Server that processes files from a non-XML-based format such as X12, EDIFACT, or delimited flat file into a valid specification based on a set of predefined parameters. The following illustration shows a flat file being sent to BizTalk Server. The file is parsed into XML by the flat file parser with the help of the file envelope.

Ee265742.legacyfileint01(en-US,BTS.10).gif

Serializers are a component of BizTalk Server that processes files from specifications or XML formats into non-XML-based file formats such as flat file, X12, and EDIFACT based on a set of rules or parameters. The following illustration shows BizTalk Server generating an X12 format file.

Receive Functions

Ee265742.legacyfileint02(en-US,BTS.10).gif

Receive functions are a component of BizTalk Server that enables any server to collect messages from external sources such as the file system or Microsoft Message Queuing. The File and Message Queuing receive functions use an event model rather than a polling model to collect messages.

  • A File receive function is activated when a file is created in a specified directory. If this file matches the required criteria, it is consumed by the receive function and submitted to BizTalk Server.
  • A Message Queuing receive function is activated when a message arrives on the designated message queue. Like the File receive function, the message, if it matches the required criteria, is consumed by the receive function and submitted to BizTalk Server.

Receive functions provide an excellent, GUI based approach to integrate external systems, especially those from legacy systems, into BizTalk Server.

To extend the functionality of a receive function and to provide more flexibility when using legacy files, additional message processing can be conducted prior to the message being submitted to BizTalk Messaging. BizTalk Server provides interfaces that enable you to create custom preprocessing components.

A simple example of preprocessor use would be the conversion of a file that has been compressed for transmission from a proprietary format back into standard ASCII prior to submission to BizTalk Messaging.

Application Integration Components

Ee265742.legacyfileint03(en-US,BTS.10).gif

An application integration component (AIC) provides a method for creating a programmatic integration point to a legacy line-of-business application. The integration point is tightly coupled, meaning that the structure and logic of the integration component are often closely aligned with a particular application and application version.

Each AIC is a component (COM object) that BizTalk Server calls to deliver data to an application.

At a summary level, an AIC provides two different methods for achieving a tightly coupled integration into the designated line-of-business applications:

  • The pipeline AIC. This will be familiar to any users of Microsoft Site Server Commerce Edition or Microsoft Commerce Server. It effectively allows the utilization of the Commerce Server pipeline components.
  • The lightweight AIC. This is provided for developers who want a lightweight model for application integration. It does not support design-time user interface or configuration properties and requires a single interface that contains a single method as an entry point. The component is implemented, and the document is passed to it.

Note that if a messaging port is configured in BizTalk Server to include the use of an AIC, then the messaging port simply functions as a new transport protocol with the component being automatically instantiated and passed the requisite data.

Scenario Overview

After extensive discussions, the Chief Technical Officer of Northwind Traders, Limited, has decided to introduce a message-based architecture enabling the company to decouple its applications and achieve a range of projected benefits, including loosely coupled integration with suppliers and partners.

Projected benefits from the new message-based environment include the ability to replace systems, or to adopt new best-of-breed solutions, with minimum impact on the existing installed technology. This change in architecture is expected to reduce ongoing system maintenance costs and total commitment to expenditure.

In addition, the specific financial benefits of the new direction will allow the company to take advantage of the investment it has made in current data formats that have been created to meet the specific needs of the business.

Northwind Traders currently has the following installed and operational line-of-business systems:

  • Northwind Traders has maintained a mainframe-based manufacturing system that handles specific activities, including stock management, bill of materials, and just-in-time ordering activities. Although this system is being phased out and its functions replaced by the new Enterprise Resource Planning (ERP) system, it is expected to remain in place for the next two years.
  • As part of the drive to reduce cost and introduce a range of enterprise-wide management efficiencies, the organization has commenced implementation of an ERP system that has already taken over key financial activities. Although the next, and future, versions will support many of the emerging XML-based industry standards, the current version has been on the market for two years and provides integration points through a proprietary flat-file format.
  • In line with the improved management information, Northwind Traders recognizes its customers as its key asset and has recently implemented a Customer Relationship Management (CRM) system. This system, deployed on Microsoft SQL Server 2000, uses many of the new database features, allowing it to support XML natively even though it does not adopt a specific industry standard.

The project team determined that this was to be done in three phases:

  • Internal application integration
  • Customer integration
  • Supplier integration

The following sections detail the integration requirements to achieve these three phases and integrate various data and file formats into the legacy system.

Internal Application Integration

As part of adopting the message-based architecture, the project team selected XML as the standard for integration of applications. Whenever possible, they adopted an industry-standard message format rather than creating their own. This enables the mapping of data between proprietary formats, including EDI, flat files, and custom XML, to a common XML format. The long-term goal is that as the available software matures and adopts emerging XML standards, Northwind Traders will be able to integrate new systems simply by passing them existing XML messages without the need for specialized integration efforts.

The following illustration is a high-level representation of the new system architecture for Northwind Traders.

Ee265742.legacyfileint04(en-US,BTS.10).gif

Application Integration Overview

Northwind Traders needed to create specifications for each of the data formats in the system. Initially both the ERP and the CRM system will integrate customer information and will produce a customer-details update message. Remembering that the selected architecture requires all messages to be translated into an industry-standard format, the following XML has been defined as the standard message type (A2A_CustomerUpdateMessage.xml). This message is created through BizTalk Editor.

<CustomerUpdates>
   <CustomerUpdate @CustomerCode>
      <AddressDetails @Type @AddressLine1 @AddressLine2 @PostalCode @State @Country>
</AddressDetails>
      <Contacts @FirstName @SecondName @Salutation @Email>
</Contacts>
      <ArchiveDetails @UserID @Date @Time @Comment>
</ArchiveDetails>
   </CustomerUpdate>
</CustomerUpdates>

This will require that both the ERP system and the CRM system have their messages translated into the standard format for distribution to the other systems. The following sample message (CustomerUpdateTransaction.XML) will be used in the system:

<CustomerUpdates>
   <CustomerUpdate CustomerCode="CC099">
      <AddressDetails Type="Delivery" AddressLine1="BizTalk Plaza" 
AddressLine2="26 Building Terrace" PostalCode="10087" State="NS" 
Country="United Land  Colony">
</AddressDetails>
      <Contacts FirstName="Mike"  SecondName="Nash"  Salutation="Capt" 
E-mail="someone@microsoft.com">
</Contacts>
<ArchiveDetails UserID="GRAYT" Date="11/10/2001"  Time="1:15AM"
Comment="Change of address">
</ArchiveDetails>
   </CustomerUpdate>
</CustomerUpdates>

Now that a standard format for the customer update has been selected, the required specifications need to be built.

ERP System Integration

First we will examine the document specifications and the envelopes for the ERP system.

Customer Update Specifications and Envelopes

The internal ERP system requires files in the following delimited flat-file format using record identifiers to differentiate the content:

1, CustomerCode,AddressType,Street,StreetAddress,DateUpdated,UpdatedBy
2, CustomerCode,FirstName,Surname,Phone,Email,DateUpdated,UpdatedBy

The ERP system is able to import this into a standard format for processing as a batch.

Information received in the standard message format is translated by the BizTalk Mapper tool. BizTalk Mapper requires two specifications to facilitate the translation of the data into the legacy format. The starting format is the standard XML message (A2A_CustomerUpdateMessage.xml), while the legacy format is represented by the specification (A2A_ERPCustomerUpdateFile.XML). The parsing information to allow BizTalk Messaging to both serialize the XML file into flat files and to parse flat files and create the matching XML document is in the specification, and the envelope type instructs BizTalk Server to serialize to a flat file specifically.

Once the specification has been created, the parsing information can be created to allow BizTalk Server to process the flat files into and out of the system. Important settings that are specified within BizTalk Editor are reviewed in the following tables.

Note that in some instances the default BizTalk Server setting has been selected specifically to highlight it. Most parameters within the specifications and envelopes have a default setting that is utilized when the user does not explicitly select an option. A comprehensive list of these can be found in the Microsoft BizTalk Server 2000 online Help.

SpecificationEditor tabPropertyComment
Root nodeReference StandardThis is set to Custom.
  Default Record DelimiterThe default record delimiter is set to CR (carriage return) or (0xd) hex and is used on the Parse tab in the specification.
  Default Field DelimiterThe default field delimiter is set to a comma or (0x2c) hex and is used on the Parse tab in the specification.
  EnvelopeThis is set to YES to specify that the specification is an envelope.
 ParseStructureThe structure for the file is delimited. This is how BizTalk Server is able to recognize or create the records within the file. This specifically descries the AddressDetails and Contacts record types.
  Field OrderThe field order is PostFix, indicating that the record delimiters appear at the end of each record. N.B. PreFix, indicating that the record delimiters appear before the record is the default, so you often need to change this setting when you are working flat files (such as .CSV).
  Delimiter TypeThe default record delimiter is selected because this has already been set. The CR (carriage return) delimiter allows BizTalk Server to determine at which point the record finishes and to validate that the data returned represents a valid record according to the specification.
  Append NewlineThis value is set to Yes. The output file required a record delimiter similar to that of a text file. This delimiter is a CRLF (carriage return line feed). By selecting to append a new line, BizTalk Server automatically adds an LF (line feed) to the end of each record during serialization from the XML document to the flat file.
  Skip Carriage ReturnThis value is set to No. Because files sometimes have superfluous CR and LF characters to make the file readable, BizTalk Server enables you to automatically skip these when parsing the file. In this instance the default record delimiter is CR, which specifies to process this character.
  Skip Line FeedThis value is set to Yes. In this instance the default record delimiter is CR, and the additional LF during the parsing process can be skipped.
AddressDetailsReferenceMaximum OccurrencesThis value is set to * because this record type can appear multiple times.
 ParseStructureThe structure for the AddressDetails records will be Delimited.
  Source Tag IdentifierThis value is set to 1. The record identifier for address details is the record type.
  Field OrderThe field order is InFix, indicating that the field delimiters within this record are between the fields.
  Delimiter TypeThe default field delimiter is selected because this has already been set to a comma. This enables BizTalk Server to determine where the data starts and ends and to validate that the data returned represents a valid record according to the specification.
AddressDetails/

StreetAddress1

ParseWrap CharacterThis value is set to quotation marks or (0x22) hex. This allows the field to be output and to include characters such as commas that have been specified as field delimiters.
AddressDetails/

StreetAddress2

ParseWrap CharacterThis value is set to quotation marks or (0x22) hex. This allows the field to be output and to include characters such as commas that have been specified as field delimiters.
ContactsReferenceMaximum OccurrencesThis value is set to * because this record type can appear multiple times.
 ParseStructureThe structure for the Contacts records will be Delimited.
  Source Tag IdentifierThis value is set to 2. The record identifier for address details is the record type.
  Field OrderThe field order is InFix, indicating that the field delimiters within this record are between the fields.
  Delimiter TypeThe default field delimiter is selected because this has already been set to a comma. This enables BizTalk Server to determine where the data starts and ends and to validate that the data returned represents a valid record according to the specification.

Important Concepts

One of the important features provided by BizTalk Editor when processing and generating legacy files is the wrap character. The wrap character enables the author to enclose field data and is of specific relevance when dealing with delimited files. In some instances the data itself contains the delimiter character. For example, a street address might be represented as:

1st Floor, 25 Test Street., Manchester

In a comma-delimited file, this causes the single data field to be interpreted as two distinct fields. By utilizing the wrap character, a given data field can be isolated regardless of its content. Therefore, our previous example would become:

"1st Floor, 25 Test Street.",Manchester

The comma between the double quotation marks is interpreted by BizTalk Editor to be field data rather than a delimiter value.

Testing Your Specification

Once created, the specification can be validated against the sample file. This enables you to ensure that the specification can parse the data output from the ERP system and serialize data into a format for processing by the ERP system. The ERPCustomerUpdateBatch.dat file is a batch file from the ERP system. Use BizTalk Editor to load the new specification and use the Validate Instance option from the Tools menu to ensure that the parsing rules entered match the flat file. The sample file is found in the C:\Whitepaper samples\Sample Data directory.

Updating BizTalk Server

Once the specifications and envelopes have been created, they must be registered within BizTalk Server, and the relevant ports, channels, and applications must be created. Note: If you have run the installation script then you have already completed this step. The definition of ports, channels, organizations, and applications is covered in detail in the Microsoft BizTalk Server 2000 online Help, and is dealt with specifically in the online tutorial.

TypeFormatNameComment
EnvelopeFLATFILEERPCustomerUpdateEnvelopeThe format tells BizTalk Server what parser to use when serializing or parsing the file.
Specification-ERP Customer Update FileThis is the specification describing the XML incarnation of the final file format. It uses the document specification A2A_ERPCustomerUpdateFile.
Specification-Customer Update MessageThis is the specification describing the XML file holding the updates to be processed.
Application-MessagingSystemBecause all messages are being handled by a central system, this has been entered into BizTalk Server as an application in its own right.
Port-Backoffice ERP SystemThis outputs a file for processing by the ERP system.
Channel-ERP Customer UpdateThis transforms the standard message to the XML representation of the data required by the ERP system and delivers it to the port.
Port-Backoffice CRM SystemThis outputs a file for processing by the CRM system.
Channel-CRM Customer UpdateThis transports the standard XML message to the port.

System Processing Overview

Ee265742.legacyfileint05(en-US,BTS.10).gif

  1. The required message arrives from the messaging system, .\Whitepaper Sample\A2A Processing\. Once collected by the receive function, CustomerUpdateProcessing, it is delivered into the channel. Because two channels are specified to receive this document format, BizTalk Server sends a copy to both channels, ERP Customer Update and CRM Customer Update.
  2. Once in the channel, the document is matched and validated against the standard customer update specification.
  3. ERP channel. A map transforms the standard format message into a format specifically for the ERP system before delivering it to the Port to Backoffice ERP System port. CRM channel. The XML file is validated against the output specification before being delivered to the Port to Backoffice CRM System port.
  4. ERP channel. The designated envelope, ERPCustomerUpdateEnvelope, transforms the XML message into a flat file following all the rules and parameters containined in the envelope. CRM channel. The XML file is output directly to the file system.
  5. Once complete, the file, now containing the new format, is delivered to the file system.

Testing the Scenario

To test this scenario, install the article sample system using the instructions included in the appendix. Once set up, the process is activated by dropping a copy of CustomerUpdateMessage.xml from the .\Whitepaper Sample\Sample Data\ directory into the .\Whitepaper Sample\A2A Processing\ directory. Once the process completes, update the following directories as follows:

.\Whitepaper Sample\A2A Processing\Should no longer have the sample file.
.\Whitepaper Sample\A2A Processing\CRM System\Should now contain an additional .xml file.
.\Whitepaper Sample\A2A Processing\ERP System\Should now contain an additional .dat file.

Integrating Customers with EDI and Flat Files

After successfully completing the enterprise application integration, Northwind Traders proceeded to phase two, customer integration. Northwind Traders believed that two key business imperatives can be achieved by direct integration with its customers. These are:

  • Maintaining its position as an organization that is responsive to customer requests.
  • Reducing the cost per transaction, which is a key metric in the business-to-business environment, in order to gain additional competitive advantage.

After analysis of the key transactions processed, purchase orders were identified as a transaction that is processed in significant numbers with a considerable number of keying errors. By introducing purchase orders into the company as an STP (Straight Through Processing) transaction, they will be able to eliminate re-keying errors, increase the number of transactions processed, and improve the rate of responsiveness to customer queries. This required provision for the following scenarios:

  • EDI support for purchase orders supplied in an X12 4010 850 format.
  • Support for a delimited format.

While the ideal scenario is to utilize XML for the point-to-point integration with customers, the customers are heavily dependent on legacy technology, specifically electronic data interchange (EDI) and proprietary flat-file formats. By utilizing standard features in BizTalk Server, including support for EDI and XML, auditing, tracking, and encryption, Northwind Traders is able to implement its business-to-business integration in a timely manner.

EDI Integration

The following section contains an overview of the key aspects of EDI only. EDI is the subject of another article and should be referred to for a more detailed review of the topic.

To enable the integration of EDI purchase orders, Northwind Traders utilized the standard specifications provided by BizTalk Server. This included the following specifications found in the templates directory of BizTalk Server:

  • EDI - X12 4010 870 specification for purchasing
  • EDI - X12 4010 997 specification for receipts
  • XML - Common purchase order
  • XML - Canonical receipt

When setting up EDI specifications, in this instance specifically X12, it is important to include the document selection criteria. Selection criteria are a unique set of name-value pairs that BizTalk Server uses when processing EDI documents. In the instance of X12 documents, the server uses selection criteria to uniquely identify and select a document definition because no document definition name is available within individual EDI documents.

The specification for the standard X12 4010 documents has not been included in this paper due to the document size; the specification is, however, available in the accompanying sample and in any default BizTalk Server installation.

When working with EDI X12 standards, make sure that you provide information that enables BizTalk Server to identify the documents correctly. In this instance the following settings are required to enable BizTalk Server to correctly select and process the X12 document. These settings represent the document's property set and are entered on the Selection Criteria tab during document creation within BizTalk Messaging. An example of the correct selection criteria can be found in the X12 specification, X12 850 Purchase Order, included in the samples.

functional_identifier      Equates to field GS01; should be a two-character field

application_receiver_code   Equates to field GS03; should be a code 2 to 15 characters long

application_sender_code      Equates to field GS02; should be a code 2 to 15 characters long

standards version      Equates to field GS08; should be a number

Updating BizTalk Server

The specifications and envelopes must be registered within BizTalk Server and the relevant ports, channels, and applications created. Note: If you have run the installation script then you have already completed this step.

TypeFormatNameComment
EnvelopeX12X12The format tells BizTalk Server what parser to use when parsing the file. No specification is required.
SpecificationX12 4010X12 850 Purchase OrderThis is the specification describing the EDI format of the purchasing file.
SpecificationX12 4010X12 997 Acknowledgement/ReceiptThis is the specification describing the EDI format of the receipt to be sent to the customer.
SpecificationXMLCommon Purchase OrderThis is the specification describing the common purchase order and its associated XML structure.
SpecificationXMLReceiptThis is the specification describing the canonical receipt and its associated XML structure.
Port-Incoming Purchase OrdersThis outputs a file into the inbound directory for the company.
Channel-Incoming X12-850 Purchase OrderThis transforms the EDI standard message to the XML representation of the data required by the system and delivers it to the port.

Testing the Scenario

To test the process drop a copy of PurchaseOrder.edi from the .\Whitepaper Sample\Customer A\Sample Data\ directory into the .\Whitepaper Sample\Customer A\Documents Out\ directory. Once the process is completed, update the following directories as follows:

.\Whitepaper Sample\Customer A\Documents Out\Should no longer have the sample file.
.\Whitepaper Sample\Customer A\Documents In\Should now contain an additional .edi file.
.\Whitepaper Sample\Documents In\Should now contain an additional .xml file.

System Processing Overview

Ee265742.legacyfileint06(en-US,BTS.10).gif

  1. The required message arrives from the customer in an EDI format and is collected by the receive function, Receive Purchase Order.
  2. BizTalk Server uses its EDI parser to process the document into XML and pass it into the channel, where it is validated against the required specification. In addition, it generates a receipt to acknowledge the successful arrival of the customer's purchase order. Receipt generation functionality is provided by BizTalk Server when dealing with EDI documents and is covered in more detail in the EDI article.
  3. The map reformats the message from the EDI style to the common purchase order format.
  4. The channel outputs the purchase order to the port.
  5. The port delivers the message to the file system.
  6. BizTalk Server delivers a canonical receipt to the designated receipt channel.
  7. The receipt channel maps the canonical receipt to the required X12 4010 997 receipt format.
  8. The receipt channel validates the message against the output specification and delivers the message to the port.
  9. The X12 formatted receipt is delivered onto the file system.

Flat File Integration

Some customers, using a popular industry-specific legacy system, deliver purchase order files in a positional format.

The parsing function translates the information received from the customer. The specification for the inbound message and therefore the parser is found in FixedLengthPurchaseOrder.xml.

Once the specification has been created, the parsing information can be created to allow BizTalk Server to process the flat files into and out of the system. Important settings that are specified within BizTalk Editor are shown in the following tables.

SpecificationEditor tabPropertyComment
Root NodeReference StandardThis is set to Custom.
  Default Record DelimiterThe default record delimiter is set to CR or (0xd) hex and is used on the Parse tab in the specification.
 ParseStructureThe structure for the file will be both delimited and positional. This is the root node and describes the records within it. The records are positional, but each record is separated from the others by a CR (carriage return).
  Field OrderThe field order is PostFix, indicating that the record delimiters will appear at the end of each record.
  Delimiter TypeThe default record delimiter is selected since this has already been set. The CR (carriage return) delimiter allows BizTalk Server to determine at which point the record finishes and to validate that the data returned represents a valid record according to the specification.
  Skip Carriage ReturnThis value is set to No. Because files sometimes have superfluous CR and LF characters to make the file readable, BizTalk Server allows you to automatically skip these when parsing the file. In this instance the default record delimiter is CR and is specified to be processed.
  Skip Line FeedThis value is set to"Yes. In this instance the default record delimiter is CR, and the additional LF can be skipped during the parsing process.
HeaderReferenceMaximum OccurrencesThis value is set to 1, indicating that only a single header line can appear.
 ParseStructureThe structure for the header records will be Positional
  Source Tag IdentifierThe value is H, indicating that the record is identified from all the others by the constant value H.
  Source Tag PositionThis value is 1, indicating that the first position in the file is where the Source Tag Identifier will appear.
Header/OrderDateReferenceRequiredThis value is set to Yes, indicating that the data field is required.
  Start PositionThe value is 2, indicating that the field starts at position 2.
  End PositionThe value is 20, indicating that the field ends at position 20.
Header/OrderNumReferenceRequiredThis value is set to Yes, indicating that the data field is required.
  Start PositionThe value is 10, indicating that the field starts at position 10.
  End PositionThe value is 20, indicating that the field ends at position 20.
DetailReferenceMaximum OccurrencesThis value is set to *, indicating that any number of DETAIL lines can occur.
 ParseStructureThe structure for the Detail records will be Positional.
  Source Tag IdentifierThe value is D, indicating that the record is identified from all the others by the constant value D.
  Source Tag IdentifierThe value is 1, indicating that the first position in the file is where the Source Tag Identifier will appear.
Detail/Product CodeReferenceRequiredThis value is set to Yes, indicating that the data field is required.
  Start PositionThe value is 2, indicating that the field starts at position 2.
  End PositionThe value is 11, indicating that the field ends at position 11.
Detail/QtyReferenceRequiredThis value is set to Yes, indicating that the data field is required.
  Start PositionThe value is 12, indicating that the field starts at position 12.
  End PositionThe value is 16, indicating that the field ends at position 16.
Detail/ValueReferenceRequiredThis value is set to Yes, indicating that the data field is required.
  Start PositionThe value is 17, indicating that the field starts at position 17.
  End PositionThe value is 25, indicating that the field ends at position 25. By default the field is left-justified and padded with spaces so no additional information can be specified.

Once created, the specification can be validated against the sample file. This enables you to ensure that the specification can parse the data provided by the customer and convert it to XML that can be utilized by the messaging system that Northwind Traders is implementing. The FlatFilePurchaseOrder.dat file is a sample file from the customer and can be found in the C:\Whitepaper Samples\CustomerB\Sample Data\ directory. Use BizTalk Editor to load the new specification and use the Validate Instance option from the Tools menu to ensure that the parsing rules entered match the flat file.

Updating BizTalk Server

Once the specifications and envelopes have been created, they must be registered within BizTalk Server and the relevant ports, channels, and applications created. Note: If you have run the installation script then you have already completed this step.

TypeFormatNameComment
EnvelopeFLATFILECustomerTypeBThe format tells BizTalk Server what parser to use when serializing or parsing the file
SpecificationXMLFixed Length Purchase OrderThis is the specification describing the XML incarnation of the file format. It uses the FixedLengthPurchaseOrder.xml specification.
Port-Incoming Purchase OrdersOutputs a file into the Documents In directory for processing.
Channel-Fixed Length Purchase OrderTransforms the standard message to the XML representation of the data required by the ERP system and delivers it to the port.

System Processing Overview

Ee265742.legacyfileint07(en-US,BTS.10).gif

  1. The required message arrives from the customer site, .\Whitepaper Sample\Customer B\Documents Out\, and is delivered into the channel, Fixed Length Purchase Order, by the receive function, Receive Order From Customer B.
  2. Once in the channel, the document is parsed from its flat file format and converted to an XML format that matches the inbound specification. The parser uses the properties entered on the Parse tab within BizTalk Editor as the basic parsing parameters.
  3. The document is validated against the outbound XML specification and delivered to the port.
  4. Once complete, the file, now containing the new format, is delivered onto the file system.

Testing the Scenario

To test this scenerio drop a copy of FlatFilePurchaseOrder.dat from the .\Whitepaper Sample\Customer B\Sample Data\ directory into the .\Whitepaper Sample\Customer B\Documents Out\ directory. Once the process completes, update the following directories as follows.

.\Whitepaper Sample\Customer B\Documents Out\Should no longer have the sample file.
.\Whitepaper Sample\Documents In\Should now contain an additional .xml file.

Integrating Suppliers with Flat Files

Northwind Traders deals with three main suppliers who use the same popular mainframe-based legacy system. For this reason, queries on stock availability are distributed into and out of the system in positional formats, similar to traditional COBOL copybooks.

The serializing function of BizTalk Server translates information into a format to be sent to the supplier. The specification for the outbound document and therefore the serializer is found in FixedLengthStockQuery.xml.

Once the specification has been created, the serializing information can be created to enable BizTalk Server to process the XML files into the required flat file format. Important settings that are specified within BizTalk Editor are shown in the following tables.

SpecificationEditor TabPropertyComment
Root NodeReference StandardThis is set to Custom.
 ParseStructureThe structure for the file is Positional.
HeaderReferenceMinimum OccurrencesThis value is set to 1, indicating that only a single header line can appear.
 ReferenceMaximum OccurrencesThis value is set to 1, indicating that only a single header line can appear.
 ParseStructureThe structure for the header records will be Positional.
ProductCodeReferenceRequiredThis value is set to Yes, indicating that the data field is required.
  Start PositionThe value is 1.
  End PositionThe value is 11.
QtyReferenceRequiredThis value is set to Yes, indicating that the data field is required.
  Start PositionThe value is 12.
  End PositionThe value is 16.
PackageReferenceRequiredThis value is set to Yes, indicating that the data field is required.
  Start PositionThe value is 17.
  End PositionThe value is 25.
 DeclarationData Type ValueThis has been set to Enumeration and therefore allows the entry of a range of valid values into the Data Type Values field on the same page.

Once created, the specification can be validated against the sample file. This enables you to ensure that the specification can parse the data provided by the customer and convert it to XML that can be utilized by the messaging system that Northwind Traders is implementing. The FlatFileStockQuery.dat file is a sample file for the supplier. Use BizTalk Editor to load the new specification and use the Validate Instance option on the Tools menu to ensure that the parsing rules entered match the flat file.

Updating BizTalk Server

Once the specifications and envelopes have been created, they must be registered within BizTalk Server and the relevant ports, channels, and applications created.

TypeFormatNameComment
EnvelopeFLATFILEFlatFileSupplierThe format tells BizTalk Server what serializer to use when serializing the file.
SpecificationXMLFlat File Stock QueryThis is the specification that describes the XML message and that also holds the rules for creating the flat file. It uses the specification FixedLengthStockQuery.xml.
Port-Outbound Stock QueriesThis outputs a file into the Documents In directory for processing.
Channel-Stock QueriesThis transfers the initial XML query to the port.

System Processing Overview

Ee265742.legacyfileint08(en-US,BTS.10).gif

  1. The required stock query message arrives from an internal application. This message comes from the customer site, .\Whitepaper Sample\ Documents Out\, and is delivered into the channel, Stock Queries, by the receive function, Request Stock Availability.
  2. Once in the channel, the document is validated against the inbound document specification.
  3. The document is validated against the outbound XML specification and delivered to the port.
  4. The outbound message is then processed by the serializer. Using the parameters entered on the Parse tab of BizTalk Editor and the envelope (FlatFileSupplier), the message is formatted as a flat file and delivered to the file system.

Testing the Scenario

To test this scenario drop a copy of StockQuery.xml from the .\Whitepaper Sample\Sample Data\ directory into the .\Whitepaper Sample\ Documents Out\ directory. Once the process completes, update the directories as follows:

.\Whitepaper Sample\ Documents Out\Should no longer have the sample file.
.\Whitepaper Sample\Supplier A\Documents In\Should now contain an additional .dat file.

Common Issues

The following are four of the more common questions about legacy files that do not have immediately obvious solutions. These are clearly documented in the online documentation and reproduced here for reference when working with the examples in this paper.

Flat file not completely parsed when submitted to BizTalk Server

A delimited flat file might have a parsing error when submitted to BizTalk Server if the file has the following characteristics:

  • The Field Order property for the root note is set to Prefix or Postfix.
  • The name of the root node is a substring of the name of another node in the file.

The solution to this is to rename the root node so that its name is not a substring of the name of any other node in the specification. Also, ensure that the document submitted matches the specification it is being validated against. You can do this by using the Validate Instance property on the Tools menu of the specification editor.

White space not preserved in flat file submitted to BizTalk Server

When a flat file is submitted to BizTalk Server, white space in fields might be trimmed. This is because, by default, the underlying MSXML parser does not preserve white space in a field with its Type property (on the Declaration tab) set to Element.

If it is important to preserve white space in a field contained in a flat file, in BizTalk Editor be sure to set the Type property on the Declaration tab of the field in the source specification to Attribute.

Server does not return all documents in a flat file interchange

This is usually caused because one of the documents in the interchange does not meet the document specification. For example, one of the documents is missing a required field.

The solution is to locate the document within the interchange that does not meet the specification, fix it, and resubmit the interchange. Although an error is returned for the specific document that does not meet the specification, BizTalk Server cannot process all the documents in the interchange. The flat file structure is an open format and is not designed to implement redundancy checking.

The server returns the following error: "The X12 4010 855 document is missing the entire property set that is required for this serializer to run."

When working with EDI X12 standards, make sure that BizTalk Server has all the information required to identify the document. These criteria are represented in the document's property set and are entered on the Selection Criteria tab during document creation within BizTalk Messaging. An example of the correct selection criteria can be found in the X12 specification, X12 850 Purchase Order, included in the samples.

functional_identifier      Equates to field GS01; should be a two-character field

application_receiver_code   Equates to field GS03; should be a code 2 to 15 characters long

application_sender_code      Equates to field GS02; should be a code 2 to 15 characters long

standards version      Equates to field GS08; should be a number

Appendix: Sample Installation

A range of specifications, sample scripts, ports, and channels is provided with this paper. To install the samples, perform the following steps:

Perform a clean installation of BizTalk Server, although this is not required if you have already defined conflicting names for document definitions, channels, ports, envelopes or organizations then the setup script will fail.

Unzip the attached WhitepaperSamples.zip file onto the C drive. This creates all the required directories that include sample data.

Edit the BuildWhitepaper2.vbs script and adjust the HOSTNAME variable to the name of your server.

' Change the HOSTNAME to your server before executing this script.
Const HOSTNAME = "GRAYDEMO"

Copy the folder \Whitepaper Sample\Configuration\Maps\Whitepaper to the WebDAV repository which is located by default in \Program Files\Microsoft BizTalk Server\BizTalkServerRepository\Maps to create a \Program Files\Microsoft BizTalk Server\BizTalkServerRepository\Maps\Whitepaper folder

Copy the folder \Whitepaper Sample\Configuration\DocSpecs\Whitepaper to the WebDAV repository which is located by default in \Program Files\Microsoft BizTalk Server\BizTalkServerRepository\Maps to create a \Program Files\Microsoft BizTalk Server\BizTalkServerRepository\DocSpecs\Whitepaper folder

Execute the BuildWhitepaper2.vbs script. This builds all the dependencies and requirements for BizTalk Server.

Assuming an install on C drive, create the following four receive functions in the BizTalk Server Administrator:

Customer Update Processing   (File)

Polling location:   C:\Whitepaper Sample\A2A Processing\

File types to poll for:   *.XML

Envelope:    <None>

Channel name:    <None>

Source Selected:

    Organization Name: OrganizationName

    Organization identifier value: Home Organization

Destination Selected:

    Organization Name: OrganizationName

    Organization identifier value: Home Organization

Document definition name:    Customer Update Message

Receive Order From Customer B   (File)

Polling location:   C:\Whitepaper Sample\Customer B\documents Out\

File types to poll for:   *.DAT

Envelope:    Customer Type B

Channel name:    Fixed Length Purchase Order

Document definition name:    <None>

Receive Purchase Order   (File)

Polling location:   C:\Whitepaper Sample\Customer A\Documents Out\

File types to poll for:   *.EDI

Envelope:    <None>

Channel name:    Incoming X12-850 Purchase Order

Document definition name:    <None>

Request Stock Availability   (File)

Polling location:   C:\Whitepaper Sample\Documents Out\

File types to poll for:   *.XML

Envelope:    <None>

Channel name:    Stock Queries

Document definition name:    <None>

This is a preliminary document and may be changed substantially prior to final commercial release. This document is provided for informational purposes only and Microsoft makes no warranties, either express or implied, in this document. Information in this document is subject to change without notice. The entire risk of the use or the results of the use of this document remains with the user. The example companies, organizations, products, people and events depicted herein are fictitious. No association with any real company, organization, product, person or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Unpublished work. ©2001 Microsoft Corporation. All rights reserved.

Microsoft, BizTalk, Visual Basic, Visual C++, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft