none
Creating flat file schema ? RRS feed

  • Question

  • Hi - below is my data coming in the file. Can anyone share how can I create schema.

    Please note the file is 255 MB huge file

    A link which I can follow in creating this flat file schema correctly ?

    "HX_MEMBER_ID_I"|"HX_MEMBER_ID_X"|"HX_MEMBER_ID_MATCH"|"PROGRAM_TYPE_I"|"PROGRAM_TYPE_X"|"PROGRAM_TYPE_MATCH"|"ACCOUNT_ID_I"|"ACCOUNT_ID_X"|"ACCOUNT_ID_MATCH"|"PLAN_CODE_I"|"PLAN_CODE_X"|"PLAN_CODE_MATCH"|"PLAN_CODE_VARIANCE_I"|"PLAN_CODE_VARIANCE_X"|"PLAN_CODE_VARIANCE_MATCH"|"COVERAGE_BEGDATE_I"|"COVERAGE_BEGDATE_X"|"COVERAGE_BEGDATE_MATCH"|"COVERAGE_ENDDATE__I"|"COVERAGE_ENDDATE__X"|"COVERAGE_ENDDATE__MATCH"|"CIN_I"|"CIN_X"|"CIN_MATCH"|"CLIENT_ID_I"|"CLIENT_ID_X"|"CLIENT_ID_MATCH"|"MEMBER_STATUS_I"|"MEMBER_STATUS_X"|"MEMBER_STATUS_MATCH"|"APTC_AMT_I"|"APTC_AMT_X"|"APTC_AMT_MATCH"|"TOTAL_RES_AMT_I"|"TOTAL_RES_AMT_X"|"TOTAL_RES_AMT_MATCH"|"PRE_AMT_TOT_I"|"PRE_AMT_TOT_X"|"PRE_AMT_TOT_MATCH"|"CSR_AMT_I"|"CSR_AMT_X"|"CSR_AMT_MATCH"|"PRE_AMT1_I"|"PRE_AMT1_X"|"PRE_AMT1_MATCH"|"TOT_IND_RES_AMT_I"|"TOT_IND_RES_AMT_X"|"TOT_IND_RES_AMT_MATCH"|"OTHER_PAY_AMT1_I"|"OTHER_PAY_AMT1_X"|"OTHER_PAY_AMT1_MATCH"|"ISSUER_ASSIGNED_MEMBER_ID_I"|"ISSUER_ASSIGNED_MEMBER_ID_X"|"ISSUER_ASSIGNED_MEMBER_ID_MATCH"|"MMIS_ID_I"|"MMIS_ID_X"|"MMIS_ID_MATCH"|"FEIN_I"|"FEIN_X"|"FEIN_MATCH"|"FIPS_CODE_I"|"FIPS_CODE_X"|"FIPS_CODE_MATCH"
    "0007197522"||"N"|"E"||"N"|"0005419197"||"N"|"11177EP0000001"||"N"|"50"|||"20161001"|||"99991231"||"N"|"ZN82270D"||"N/A"|||"N/A"|"ACTIVE"||"N"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|"416.53"|".00"|"N"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|"416.53"|".00"|"N"|"630190802"||"N/A"|||"N/A"|"113434265"||"N/A"|"36061"||"N/A"
    "0007197472"||"N"|"M"||"N"|"0005458706"||"N"|"11177MC1000001"||"N"|||"N/A"|"20161201"|||"99991231"||"N"|"MX40828Y"||"N/A"|||"N/A"|"ACTIVE"||"N"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|"MX40828Y"||"N/A"|"00894519"||"N/A"|"113434265"||"N/A"|"36005"||"N/A"
    "0007197190"||"N"|"E"||"N"|"0002571933"||"N"|"11177EP0000001"||"N"|"50"|||"20161001"|||"99991231"||"N"|"ZN87480V"||"N/A"|||"N/A"|"ACTIVE"||"N"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|"416.53"|".00"|"N"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|"416.53"|".00"|"N"|"630190801"||"N/A"|||"N/A"|"113434265"||"N/A"|"36081"||"N/A"
    "0007197188"||"N"|"E"||"N"|"0002571933"||"N"|"11177EP0000001"||"N"|"50"|||"20161001"|||"99991231"||"N"|"ZN87490R"||"N/A"|||"N/A"|"ACTIVE"||"N"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|"416.53"|".00"|"N"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|"416.53"|".00"|"N"|"630190800"||"N/A"|||"N/A"|"113434265"||"N/A"|"36081"||"N/A"
    "0007197170"||"N"|"M"||"N"|"0005458409"||"N"|"11177MC1000001"||"N"|||"N/A"|"20161201"|||"99991231"||"N"|"SG71680P"||"N/A"|||"N/A"|"ACTIVE"||"N"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|".00"|".00"|"N/A"|"SG71680P"||"N/A"|"00894519"||"N/A"|"113434265"||"N/A"|"36005"||"N/A"

    and also if there is any error is the file or record it should send back error file record as

    11177|0000902125|110669806||0001650690|0001650690|11177NY0040001|05|20140501|20140501|||CANCEL|Y|Q|36005|0.00|0.00|359.26|0.00|359.26|0.00|0.00|||630056109|630056109|4|||59|20130101|V__MMIS_ID,V__ACCOUNT_ID,
    11177|00894519|113434265|0003785266|0005102208|0005102208|11177MC1000004||20151001|20151231|FR15106C||TERM|Y|M|36059|0.00|0.00|0.00|0.00|0.00|0.00|0.00||20151031|FR15106C|FR15106C||||AI|20151231|V__MED_RATE_CODE,
    11177|00894519|113434265|0001604718|0002051649|0002051649|11177MC1000001||20140901|20141130|MK65644Z||TERM|Y|M|36047|0.00|0.00|0.00|0.00|0.00|0.00|0.00||20141130|MK65644Z|MK65644Z||||14|20141130|V__MED_RATE_CODE,

    Please guide me to achieve this scenario .


    MBH


    • Edited by JaguarsJag Wednesday, November 16, 2016 8:37 PM info
    Wednesday, November 16, 2016 8:25 PM

Answers

  • Yes I did exactly took above 6 lines and validated against schema and receiving xml output fine.

    But I am talking about big file which I receive, that is failing its validation against schema.


    MBH

    FYI, your 300 MB odd file is NOT failing due to validation. It is failing because Visual Studio is 32 bit application and it is not designed to read that large data sets into memory with that it could also be due to the machine you are working on does not have enough physical memory. 

    Rachit Sikroria (Microsoft Azure MVP)

    Thursday, November 17, 2016 4:24 AM
    Moderator
  • Hello,

    Why you are validating schema with 300 MB file. 

    Just take Header, footer and two or three lines of data and validate because its same data keep on repeating till 300 mb :)

    Use 2-3 lines of data and validate your schema instead of validate against complete 300 MB file to save your time and get rid of resource errors. 


    Ram

    Wednesday, November 16, 2016 9:48 PM
  • Hi Jaguarjags,

    This error is due to the large file size not due to the data issue, 

    On the side note, it can be beneficial to look at the SSIS to push the data to db instead of Biztalk

    Regards


    Mandar Dharmadhikari

    Thursday, November 17, 2016 4:33 AM
    Moderator

All replies

  • using Flatfile schema wizard I created a schema and when I am validating instance of the file with this schema (300MB file), its throwing error as :

     error BEC2004: Exception of type 'System.OutOfMemoryException' was thrown.

    What do I need to do now ?

    I have to achieve this in BizTalk only


    MBH

    Wednesday, November 16, 2016 9:44 PM
  • Hello,

    Why you are validating schema with 300 MB file. 

    Just take Header, footer and two or three lines of data and validate because its same data keep on repeating till 300 mb :)

    Use 2-3 lines of data and validate your schema instead of validate against complete 300 MB file to save your time and get rid of resource errors. 


    Ram

    Wednesday, November 16, 2016 9:48 PM
  • Yes I did exactly took above 6 lines and validated against schema and receiving xml output fine.

    But I am talking about big file which I receive, that is failing its validation against schema.


    MBH

    Wednesday, November 16, 2016 9:50 PM
  • Yes I did exactly took above 6 lines and validated against schema and receiving xml output fine.

    But I am talking about big file which I receive, that is failing its validation against schema.


    MBH

    FYI, your 300 MB odd file is NOT failing due to validation. It is failing because Visual Studio is 32 bit application and it is not designed to read that large data sets into memory with that it could also be due to the machine you are working on does not have enough physical memory. 

    Rachit Sikroria (Microsoft Azure MVP)

    Thursday, November 17, 2016 4:24 AM
    Moderator
  • Hi Jaguarjags,

    This error is due to the large file size not due to the data issue, 

    On the side note, it can be beneficial to look at the SSIS to push the data to db instead of Biztalk

    Regards


    Mandar Dharmadhikari

    Thursday, November 17, 2016 4:33 AM
    Moderator
  • My client doesnt want to use SSIS, they want this to be achieved this by BizTalk itself

    MBH

    Thursday, November 17, 2016 4:37 AM
  • I understand..but there are ways to do things and there are right ways to do things, going with SSIS is the best and the easiest way to achieve this

    Regards


    Mandar Dharmadhikari

    Thursday, November 17, 2016 4:45 AM
    Moderator
  • My client doesnt want to use SSIS, they want this to be achieved this by BizTalk itself

    MBH

    I think we have already answered your query regarding creating flat file schema and its validation in Visual studio.

    To discuss on the debatching of your enormously large flat file, lets get back to the old thread which is still open here: https://social.msdn.microsoft.com/Forums/en-US/7502b8fd-d79b-4b3e-815d-e2571883689e/flat-file-processing-inserting-into-sql?forum=biztalkgeneral


    Rachit Sikroria (Microsoft Azure MVP)

    Thursday, November 17, 2016 4:54 AM
    Moderator
  • My client doesnt want to use SSIS, they want this to be achieved this by BizTalk itself

    You can take this as official word from the BizTalk expert community: ;)

    SSIS is an integral part of the BizTalk/Microsoft Integration Stack.

    Using SSIS for it's appropriate function is no less of a BizTalk solution than writing custom components.


    Thursday, November 17, 2016 4:09 PM
    Moderator
  • Yes I did exactly took above 6 lines and validated against schema and receiving xml output fine.

    But I am talking about big file which I receive, that is failing its validation against schema.


    MBH

    So, you know it is valid.

    When you actually processing this big file, do you get errors? If not, just move on.

    Thursday, November 17, 2016 4:47 PM