Custom EDIFACT file
I'm going to upgrade a BizTalk 2002 solution to BizTalk 2006 (or maybe BizTalk 2006 R2 if it makes things easier) and wonder if there are some ways to simplify the solution.
We are using BizTalk 2002 to import data from custom EDIFACT files into a SQL database. The files includes up to 6 different record types or header information or trailer information.
A record looks like this (where the first 4 digits are the number of characters in the record):
0298VKD+103:59909247:3::TKVKD:8:1+20040519:163829247'VAI+I:KK+CH:1234567'MGA+M:EEME+1+1+20040519'MGA+O: O+1+1+20040504'VAK+I:KKIIII+TKN:4+ABCD Efghij Klmn+Shs'FMN+I:IIII+T1:333++2'KAP+M:KEEME+1+11394000++20040518+2'KAP+O: O+1+11394000++20040503+2'ZAZ+M:MM+1284050+20040519'ZAZ+O:AA+1255260+20040504'
We have made a pre-processor to split the different record types into separate files which are picked up by separate file receive functions. (The records are not separated with any characters. We are using the first 4 digits to split them). It also replaces empty fields (++) with blanks (+ +).
We have made custom BizTalk schemas with the 6 custom EDIFACT record structures and map these to SQL datagrams. In the maps all fields are run through custom functoids to transpose empty/missing fields and missing sub records to a blank value (and transfer the name as column name) because the SQL Adapter can't import these as null by default.
My questions are:
1) Is there another simplified way to do the splitting with BizTalk 2006 or BizTalk 2006 R2 rather than building a customized Flat File Disassembler Pipeline?
2) Is there another way to import empty/missing fields and sub records into a SQL-database rather than using custom functoids.
Any help are appreciated.
Best regards,
Troels
