none
csv-file as a reference in a mapper

    Question

  • 
    

    Hi,

    I have a file with about 20 000 companies. When I map this file I need to use a .csv-file as a "lookup". I don´t want to use a db lookup, because it will generate 2 requests per each company. What is the best way to do this?

    Source file:

    <Root xmlns="http://BizTalk.Schema1>
    <Loop> 
      <Id>123</Id>
      <Company>CompanyA</Company>
     </Loop>
     <Loop>
      <Id>456</Id>
      <Company>CompanyB</Company>
     </Loop>
    </Root>

    Destination file:

    <Root xmlns="http://BizTalk.Schema2>
    <Loop>
      <Company>CompanyA</Company>
      <City>Stockholm</City>
      <Region>AB</Region>
     </Loop>
     <Loop>
      <Company>CompanyB</Company>
      <City>Copenhagen</City>
      <Region>BC</Region>
     </Loop>
      </Root>

    Csv file (containing about 300 rows):

    Id;City;Region
    123;Stockholm;AB
    456;Copenhagen;BC

    
    
    
    
    
    
    
    
    
    

    Challan

    vendredi 27 avril 2012 12:38

Réponses

  • It ended up with a solution where I have the csv-file on disk and use it as a reference file. It is described in Pro mapping in BizTalk Server 2009.

    Challan

    • Marqué comme réponse Challan lundi 2 juillet 2012 07:43
    lundi 2 juillet 2012 07:43

Toutes les réponses

  • Keep the backup of the csv file in a file location. Use scheduleTask to copy the csv file from this location to a location where BizTalk can read it.

    Use parallel convoy orchestration which will pick the source file and the csv file. Create a schema to parse the csv file.

    Use map with source as these two inputs and loop through the request, compare if the ID matches, then map the destination. In your case, you xslt to which will be easier to do this map.

    Thanks


    SKGuru

    vendredi 27 avril 2012 13:56
  • Hi,

    1. create custom functoid to do this req.

    2. Write inline C# script using scripting functoid and read data from file( make sure it should work for all environments)

    samedi 28 avril 2012 15:36
  • Thanks for your answers!

    I tried to use a parallel convoy, but when I set the recive shape to Activate=true I must use a correlation type. To use that I must promote a field. But I can´t promote a looping field. Or am I missing something?


    Challan

    samedi 28 avril 2012 20:03
  • A promoted field has to be unique - but you can use anything - so just find a unique field.  You may need a custom pipeline to promote something (e.g. part of a filename).

    If this is helpful or answers your question - please mark accordingly.
    Because I get points for it which gives my life purpose (also, it helps other people find answers quickly)

    jeudi 14 juin 2012 13:27
  • It ended up with a solution where I have the csv-file on disk and use it as a reference file. It is described in Pro mapping in BizTalk Server 2009.

    Challan

    • Marqué comme réponse Challan lundi 2 juillet 2012 07:43
    lundi 2 juillet 2012 07:43