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

Posted: October 9, 2015 in BizTalk
Tags: , , , , ,

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 in 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 challenger scenarios like:

  • Handling Record Tag Identifiers, were we will treated 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 advance 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 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 afterwards so that we can have the desired output.

Let’s the fun begin.

Comments
  1. Neal Walters says:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s