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

Following one of my last speaking engagements in 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 to writing about this topic on my blog, a topic that I already wrote about in the past, describing and explaining almost all of the topics mentioned above:

• BizTalk Server: Transform text files (Flat Files) into XML – Introduction (Part 1)
• BizTalk Server: Transform text files (Flat Files) into XML – Necessary tools and artifacts (Part 2)
• BizTalk Server: Transform text files (Flat Files) into XML – A practical example (Part 3)
• BizTalk Server: Transform text files (Flat Files) into XML – Conclusion (Part 4)

But this time, I will transform my previous session into a sequence of blog posts where I will address and explain how to solve some of the typical or common flat file 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

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 challenging 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

• Where we have:
  • Header
    • Date
  • User
    • Name|Birthyear|Address
  • Item
    • Id|Product|Specification

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

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 file 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 to Flat Files.

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

Others, how can we tune some properties afterward so that we can have the desired output?

Let’s have fun.

Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son. 

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. View all posts by Sandro Pereira