none
Scenario for Debatching files RRS feed

  • Question

  • HI

    Could anyone please let me know at what scenario we can go for debatching the files. Is it only when the file size that is being received is more or when we need to insert row by row operations in SQL.

    In case if i receive a 5MB flat file  and i wanted to insert the same in sql so it should be done only by debatching. What would be the best practise.

    please advice

    TM

    Tuesday, June 19, 2018 3:08 PM

Answers

  • Debatching is a design pattern; its needed if you have an explicit requirement or to achieve a performance improvement.

    The first step is to break down your integration scenario to identify the requirement.

    In your case, if you are getting a 5 MB Flat File; For BizTalk, it's not really a big message however it's certainly on the higher side if we are talking about getting thousands of these sent concurrently and very frequently.

    You may want to think about this holistically;

    Do you want to make sure all the messages in the file are inserted simultaneously? Are you expecting an update to already inserted message in the subsequent file? What if one record is failed to insert; do you want to retry?

    All these scenario questions will let you decide if you want to de-batch or not.

    We can submit the whole message to a SQL stored procedure and do a bulk insert there

    Alternatively, de-batch in BizTalk to have more granular level control on the messages and insert them individually

    or hybrid.

    There are some de-batching links available in MSDN or otherwise; please go through them 

    https://blog.brauwers.nl/2015/01/12/howto-split-a-flatfile-into-multiple-files-and-ensure-the-files-are-grouped-based-on-content-from-the-source-file-using-out-of-the-box-biztalk-functionality/

    https://social.msdn.microsoft.com/Forums/en-US/dd48d32d-6200-4fd9-a5a6-11e5f3ba14da/how-to-debatch-flat-file-with-seperate-header-and-trailer-schema?forum=biztalkgeneral

    https://social.technet.microsoft.com/wiki/contents/articles/31367.biztalk-de-batching-options.aspx

    • Marked as answer by TimMat Thursday, June 21, 2018 9:34 AM
    Tuesday, June 19, 2018 7:58 PM

All replies

  • Debatching is a design pattern; its needed if you have an explicit requirement or to achieve a performance improvement.

    The first step is to break down your integration scenario to identify the requirement.

    In your case, if you are getting a 5 MB Flat File; For BizTalk, it's not really a big message however it's certainly on the higher side if we are talking about getting thousands of these sent concurrently and very frequently.

    You may want to think about this holistically;

    Do you want to make sure all the messages in the file are inserted simultaneously? Are you expecting an update to already inserted message in the subsequent file? What if one record is failed to insert; do you want to retry?

    All these scenario questions will let you decide if you want to de-batch or not.

    We can submit the whole message to a SQL stored procedure and do a bulk insert there

    Alternatively, de-batch in BizTalk to have more granular level control on the messages and insert them individually

    or hybrid.

    There are some de-batching links available in MSDN or otherwise; please go through them 

    https://blog.brauwers.nl/2015/01/12/howto-split-a-flatfile-into-multiple-files-and-ensure-the-files-are-grouped-based-on-content-from-the-source-file-using-out-of-the-box-biztalk-functionality/

    https://social.msdn.microsoft.com/Forums/en-US/dd48d32d-6200-4fd9-a5a6-11e5f3ba14da/how-to-debatch-flat-file-with-seperate-header-and-trailer-schema?forum=biztalkgeneral

    https://social.technet.microsoft.com/wiki/contents/articles/31367.biztalk-de-batching-options.aspx

    • Marked as answer by TimMat Thursday, June 21, 2018 9:34 AM
    Tuesday, June 19, 2018 7:58 PM
  • You should be able to do a batch insert into SQL as well, is there any reason you don't?

    Debatching when you don't need to add extra overhead

    Wednesday, June 20, 2018 2:06 AM
  • You can always do a bulk insert, even if the message is a large file it can be achieved using optimal performance if you use proper design to insert the message, if it is a simple message, I would suggest you to go with bulk insert.

    Have a look at BizTalk : Analysis of Direct Mapping vs XDocument Pipeline vs Streaming Pipeline To Process Large Messages for SQL Bulk Insert


    Mandar Dharmadhikari

    Wednesday, June 20, 2018 9:29 AM
    Moderator