none
How to join 16 different flat files in BizTalk orchestration RRS feed

  • Question

  • Hi BizTalk gurus ! I have 16 flat files(csv) with different schemas on input folder and have to join them according to the context they have (each file relates to another by some key-unique column). After joining the data have to be send to Dynamic CRM . We have to find out could BizTalk solve the problem in easy way or it is much more straightforward to write our own c# application that solves the problem .
    Thursday, April 6, 2017 7:49 AM

All replies

  • source CSV and target is Dynamics CRM - why are you not using the bulk import in CRM ? As far as the inter-CSV relationships are concerned, you need to sequence the imports.

    Regards.

    Thursday, April 6, 2017 7:56 AM
  • Hi,

    Will there always be 16 files??At what interval they appear for process ?? one way can be to pick up the file using the sequential convoy and then aggregate the files together.

    Regards


    Mandar Dharmadhikari

    Thursday, April 6, 2017 8:37 AM
    Moderator
  • We couldnt find anything related to the bulk insert from biztalk coul you describe it pls?

    Thursday, April 6, 2017 9:00 AM
  • All the files are put to the FTP at the same time . We can take interval max=10 minute and wait for the files in the interval . After that we have to join them together and send to the CRM . Actually each of files has many rows and each of rows should be inserted (or updated) in CRM as unique entity . Currently we end up with model where different flat files converted to the XML (in receive pipeline) and sentto the orchestration . Now we have to join them together and push to CRM 
    Thursday, April 6, 2017 9:05 AM
  • Bulk Import is available in Dynamics CRM and has nothing whatsoever to do with BizTalk.

    When updating data into CRM from BizTalk you encounter more issues w.r.t import times since BizTalk will either use the Web API or the XRM endpoints to insert the data one insert/record at a time. Secondly, as you insert data with linked entities, the complexities of integration from within BizTalk increase as you need to first execute Retrieve/RetrieveMultiple to obtain GUID's pertaining to the linked entities before executing the create/execute/update call to push in the data. Next all calls from BizTalk will land up on the IOrganization service which will increase the load on the CRM front-end servers and start affecting (if not sized) the end-user experience.

    Regards.


    • Edited by Shankycheil Thursday, April 6, 2017 9:14 AM count
    Thursday, April 6, 2017 9:13 AM
  • Also as it turns out from name sequential convoy should get file in sequence . But we have  to handle it on fly . Let me clarify . When receive port finds file it send it to the receive pipeline which converts it and sends to the orchestration . When orchestration gets file it knows nothing about it (which of the 16 files it is). It doesnt know is it fist of last doc but it should join them all by examining context or using maps .
    Thursday, April 6, 2017 9:17 AM
  • If it "has nothing whatsoever to do with BizTalk"  then how we can do bulk insert from BizTalk ?

    Thursday, April 6, 2017 9:36 AM
  • You will have to convert your CSV to XML (flat file dissembler) then de-batch the multiple records, channel them through a singleton which use the CRM IOrganization service endpoints of create, update, etc... to achieve the data insertion.

    So roughly if you have 25 distinct imports then you'd need 25 such processes as I'm assuming one import would relate to one entity in CRM.

    So my basic question was why do it in BizTalk or what gave you the impression that this would be simpler to do in BizTalk?

    Regards.

    Thursday, April 6, 2017 10:49 AM
  • Actually beside  de-batching we have to join them all before sending to CRM . If some of the files is are not in the folder or damaged , then no one of them should be sent to the CRM . According to the Microsoft documentations BizTalk aimed to create connections between different application and need to help in converting different formats. Currently we just examining it as the appropriate tool . 

    Thursday, April 6, 2017 11:44 AM
  • Ah, CRM....so....do they really have to be joined?  I ask because I've done this and that's not always the case.

    For the one case where CRM for sure requires a specific order, you just have to use an Orchestration to Correlate on the key, then the messages in CRM order, queuing the ones that have to wait.

    One thing to confirm, it's likely the case where CRM only needs certain types in order, once those are posted, any others can go in any order.

    Thursday, April 6, 2017 1:51 PM
    Moderator
  • Hi Edgar,

    Push all the messages to a local SQL database.
    Have a receive location query the database and extract all the messages when message count = 16.

    Best regards

    Rasmus Jaeger



    Tuesday, May 2, 2017 1:14 PM
  • Hi,

    you can utilize the

    1. Aggregator pattern in orchestration to merge the files( https://msdn.microsoft.com/en-us/library/aa561747.aspx) 

    2. If you are not worried on the Sequential merge, you can do with Message only solution without an Orchestration

    In the File send port-->Set the file mode to append; this will merge all the relate files based on the filter at send port.

    If the file is containing the header, you can remove the header with the pipeline component at the PreAssemble stage and add final header before sending to the CRM with another Rcv and send port on a scheduled basis. This will create an additional hub for message processing.


    Regards, Vignesh S

    Wednesday, May 3, 2017 5:35 AM