combine output from multiple orchestrations into a single csv file RRS feed

  • Question

  • I need help getting my head around how to accomplish a task in BizTalk.  Any pointers are appreciated. 

    In a nutshell, I need to gather data every two hours for orders that have changed.  This data will have to be assembled and written in a single .csv file to be sent to the customer. 

    So for example, suppose 40 orders have changed since the last run of the process.

    For each of those orders, I will need to gather order and shipping data and create a .csv data to be sent to the customer, but all the data must be sent in a single file and the file must have sequential file number as part of the file name.

    My current thinking is to use a Sql Receive Location to query for the orders that have changed and for each order, create an instance of an orchestration to process the individual order.  After all of the orders have been processed, then a custom send pipeline component will somehow assemble all the data into a single file.

    I want to take advantage of BizTalk's ability to process data in parallel, but I'm having trouble figuring out how to comibe the results into a single file for transmission. 

    How do I tell BizTalk's send pipeline to wait until all the 40 orders have been processed before combining them into a single .csv file? 

    I suppose each orchestration instance can write a temporary file to disk and the custom send pipeline component could then combine the files into a single file for transmission, but again, how would the send pipeline know when to proceed?  And that seems hackish to me and I wonder if there is a better way.

    Again, thanks for any pointers or suggestions.  I hope this makes some sense.


    Monday, December 5, 2011 8:28 PM


All replies

  • If you dont have the header for the CSV file, you can set the "Copy Mode" property to "Append" so that all the send ports will keep appending the data to the same file.

    Note: Since the file name should be the same, you should have a common place from wehere you can read the sequence number and construct your file name inside the orchestration.


    Monday, December 5, 2011 8:37 PM
  • What is the size of the data you will select from the database? What average size do you expect the CSV file to be? Is the mapping going to be heavy?

    I'm not sure that for such simple business flow there is a justification to create 1 orchestration per order, unless you are talking about very big amounts of data for each order and a heavy mapping perhaps that you will need to perform.

    40 orchestrations in the memory may cause overhead and have cost in memory, CPU and IO.

    Monday, December 5, 2011 10:35 PM
  • In most cases, the .csv's will be about 20K - 40K.  Some can be as large as 500K, but that is more the exception, not the rule.  The size is primarily determined by how many orders are processed during the two hour window and how many details are on each order.

    The initial query in the Sql Receive Location will probably return no more than 200 rows, and usually less than that.   It is possible on the rare occasion it could return more.  But the data for each of those rows will be comparatively greater.  For each order, we will query order information, detail information, and shipping information.   I know I could write code to process the orders one after the other in a pipeline component or something, but I wanted to see if BizTalk could help make the process more efficient.

    I'm just looking for suggestions on how best to accomplish my goal.



    Tuesday, December 6, 2011 5:33 PM
  • In this scenario I would consider processing the orders as a batch, and not split to an orchestration per order. I would consider perhaps that the initial query will return also the data for each order so you can reduce the round trips to the database.
    Tuesday, December 6, 2011 6:08 PM
  • Hi Chris,

    See the example can be found in BizTalk Patterns part 1 – Asynchronous Aggregation.


    Steef-Jan Wiggers

    Ordina ICT B.V. | MVP & MCTS BizTalk Server 2010

    http://soa-thoughts.blogspot.com/ | @SteefJan

    If this answers your question please mark it accordingly

    Tuesday, December 13, 2011 8:12 PM
  • Thanks for that, although we have decided to go in a different direction for this one feature.  I will definitely study this example, though.


    Tuesday, December 13, 2011 10:48 PM