BizTalk Server: Teach me something new about Flat Files (or not) – Introduction

Following one of my last speaking engagement in the Porto.Data Community Meeting about Flat Files: How to process Flat Files documents (TXT, CSV …) in BizTalk Server where I address some of the following topics:

  • How to process Flat Files documents (TXT, CSV …) in BizTalk Server.
  • What types of flat files are supported?
  • How is the process of transforming text files (also called Flat Files) into XML documents (Syntax Transformations)
  • Where does it happen and which components are needed
  • Or how can I perform a flat file validation?
  • And so on

I decided to return writing about this topic on my blog, a topic that I already wrote in the past describing and explaining almost all of the topics mention above:

But this time transforming my previous session in a sequence of blog posts where I will address and explain how to solve some of the typical or common flat files structures and scenarios.

The simplest ones:

  • Text Files Delimited by symbols
    • For example:
Sandro;Pereira;1978;Crestuma;4415
Vânia;Braziela;1981;Vila Nova de Gaia;4400
José;Silva;1972;Crestuma;4415
Rui;Barbosa;1975;Lever;4415
    • Where we have: Name,Surname,Birthyear,Address,ZipCode
  • Positional Text Files
    • For example:
0120200300 01 PT        Sandro      Pereira         Rua Central, 133      Crestuma    Vila Nova de Gaia  4415 Crestuma
0120200300 01 PT        José        Silva           Crestuna              Crestuma    Vila Nova de Gaia  4415 Crestuma
0120200300 01 PT        Rui         Barbosa         Porto                 Porto       Porto              4400
0120200300 01 PT        Miguel      Silva                                 Porto       Porto              4415 Crestuma
    • Where we have:
GroupId|PrimaryKey|SecondaryKey|ControlFlag|PartyNo|Remark|Country|Name|Surname|Street|City|District|ZipCode

0120|20|03|0|0 |01 |PT |Sandro |Pereira |Rua Central, 133 |Crestuma |Vila Nova de Gaia |4415 Crestuma

And evolve to more challenges scenarios like:

  • Handling Record Tag Identifiers, where we will treat each Tag Identifier differently
    • For example:
PO1999-10-20
USERSandro Pereira,1978-04-04, Crestuma
USERCelso Pereira,1978-04-04, Crestuma
ITEM872-AA|Super Bock|Green
ITEM871-AA|Super Bock|Black
East Coast Facility
ERROR102|0|High|Sprocket query fails.|1999-05-31T13:20:00.000-05:00
ERROR16502|2|Low|Time threshold exceeded.|1999-05-31T13:20:00.000-05:00
8675309
    • Where we have:
      • Location
      • ERROR
        • ID|Type|Priority|Description|DateTime
      • BatchID
  • Or simple How can I remove a simple header from a Text File
    • For example
P_ABB,NUMBER,SHIP_ID,CREATE_DATE,SHIP_NO,SHIP_DATE,SHIP_DEST,PO_NO,LOT_TYPE,LOT_NO,SHIP_QTY,SHIP_PRD_NO,OBS_LOT
PA1,IN1900011,K1-N-A1,11/9/2015,BA511,15/9/2015,GTW,9871119,K,LTSGT 11A,12,PD806-D0,AGT;AKT;LOOP
PA1,IN1900012,K1-N-A1,11/9/2015,BA511,15/9/2015,GTW,9871119,K,LTSGT 11A,12,PM806-D0.0J,AGT;AKT;FLAT
    • Where the structure of the message is described in the header (first line) – This scenario, for example, is very common in CSV files.

Until we reach and address an advanced topic like:

  • Debatching Flat Files messages
  • Perform Deep Validation in flat files processes
  • Create generic Receive and Send Pipelines and how to configures this pipelines through BizTalk Administration Console
  • And so on

And hopefully teaching you something new (trick or tip) about Flat Files or at least trying to demystify some issues related with Flat Files.

Most of this things will be solved by explaining to you how to create, for each scenario, flat file schema from flat file document instances using the BizTalk Flat File Schema Wizard.

Others, how can we tuning some properties afterward so that we can have the desired output.

Let’s the fun begin.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

  • Has anyone figured out why Microsoft, the maker of both BizTalk and Excel, doesn’t have an adapter for Excel in BizTalk?

One Platform Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

One Platform - Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

One Platform - Operations, Monitoring and Analytics Software
ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

Back to Top