none
Flat file processing - inserting into sql RRS feed

  • Question

  • Hi - this is not HL7 file.

    It is regular flat file which is like 200MB in size, if I bring this into BizTalk turning into XML will be 1000s of MB and inserting will be crazy for such big file.

    How can I process this easily ?  what is best approach ?


    MBH

    Wednesday, November 16, 2016 3:14 PM

Answers

  • In a CSV style flat file, all rows are the same structure.  It's like a Table in a file.

    A complex flat file has rows with different structure that mean different things.  Header/Details for example.

    SSIS can handle both, the complex file processing requires a couple of additional steps in the flow.

    Wednesday, November 16, 2016 4:44 PM
    Moderator
  • As a general rule BizTalk is not SSIS, BizTalk is not an ETL (Extract, Transform and Load). For your requirement the first choice will always be SSIS.

    In BizTalk processing 300 MB file utilizes lot of CPU and also causes throttling. Also it depends on how busy will be your Biztalk server with other message processing I would advice you to write small c# component that will break the flatfile and send smaller files to BizTalk. You have to review your server/hardware configuration.



    Rachit Sikroria (Microsoft Azure MVP)

    Thursday, November 17, 2016 5:14 AM
    Moderator
  • HI,

    There are some approaches which can help you here to reduce your file size after conversion in XML message.

    1)While creating Flat File Schema , use nodes name very small max 3-4 characters it would reduce the xml size too much. 

    2) Use orchestration only when you need that, if you just trying to insert the the data from your flat file to Sql then don't use orchestrations. Directly connect receive port to send port and USE flat file -> SP (MAP) as inboound or outbound map.

    3) Instead of one records at a time try to insert batches it will reduce your no of connection string creation and reduce the network connectivity too.

    4) Use UDT in your stored procedure to accept bulk of records at a time. Don't use Cursor in your SP , try to fetch the records from UDT itself for this in UDT you can pass a ID with incremental value.

    5) Debatch the incoming files into smaller files and then process those smaller files .

    You can also take help from the below link :

    https://msdn.microsoft.com/en-us/library/aa560481.aspx

    Regards

    Abhay Giri

    Mark this as answered or vote , if this help you.

    Thursday, November 17, 2016 6:55 AM

All replies