2

I need to get some .csv data from a rest API, map it to XML and store it in an SQL server.

The CSV to XML maps have already been completed and the SQL server is setup and ready to receive the XML documents containing the data.

The REST API I am consuming is a public one, so no credentials needed.

Here is an example of the .csv data with the included header:

sekvensnummer,tidspunkt,operation,id,kommunekode,kode,oprettet,ændret,navn,adresseringsnavn,navngivenvej_id
48354673,2018-01-14T02:08:38.432Z,insert,,0306,2901,,,Hedvigsvej,Hedvigsvej,
48352674,2018-01-14T02:08:38.432Z,insert,,0201,1187,,,Bøgeholm Alle,Bøgeholm Alle,
48252675,2018-01-14T02:08:38.432Z,insert,,0225,0938,,,Stoustrædet,Stoustrædet,
48357676,2018-01-14T02:08:38.432Z,insert,,0265,0937,,,Stouvænget,Stouvænget,
48352687,2018-01-14T02:08:38.432Z,insert,,0265,0936,,,Dyssevænget,Dyssevænget,

I am mapping this to an XML document, in this format:

<ns0:Vejstykke xmlns:ns0="http://Company.Vejregister.Schemas.Vejstykke_XML">
    <Data>
        <sekvensnummer>48352273</sekvensnummer>
        <tidspunkt>2018-01-14T02:08:38.432Z</tidspunkt>
        <operation>insert</operation>
        <id/>
        <kommunekode>0376</kommunekode>
        <vejkode>2901</vejkode>
        <oprettet/>
        <ændret/>
        <navn>Hedvigsvej</navn>
        <vejnavn>Hedvigsvej</vejnavn>
        <navngivenvej_id/>
    </Data>

I am aware that I need to use the WCF-WebHttp adapter to GET the csv data. I need to call the API once a day to fetch the most recent data.

In the configuration for the WCF-WebHttp adapter, I need to input an URI to the data. For now I have used:

/http://dawa.aws.dk/adresser?kommunekode=0326&format=csv

In the HTTP method and URL mapping I have written: GET

The receive port is set to use the CSV to XML map as an inbound map.

But no data is being picked up.

I am receiving no warnings or errors in the event log.

When is the GET request to the URI triggered? Is there a way I can call it manually?

Let me know if you need more information.

Leth
  • 1,033
  • 3
  • 15
  • 40

1 Answers1

2

In order to call the API you have to trigger it, in your scenario BizTalk has to trigger it.

Please follow the below steps to trigger it

  1. Create a Receive Port / Receive location name it as API scheduler or a friendly name, which uses the WCF-SQL and configuration as mssql://servername//BizTalkMgmtDb?InboundId=putyouridentifier

  2. Set the binding for polling interval in your case 24 hours(1 day)

example polling statement:

enter image description here

  1. Now create a two-way send port with WCF-WebHTTP adapter as you configured, with the GET method and place the filter as BTS.Receiveportname==CreatedReceivePort and pass through send pipeline and receive would be the data(CSV) with your pipeline.

Now you should be able to fetch the corresponding data easily.

Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
Suraj Revankar
  • 135
  • 9
  • Thank you for your answer. I tried following your steps, but I get an error in the event log: "The Messaging Engine failed while notifying an adapter of its configuration. "." I have configured the WCF-SQL receive location to my SQL server database. I have created a two-way send port using the WCF-WebHttp adapter and using the endpoint specified in my post. I cannot seem to find an CSV pipeline. For now I am using passthrough. It is also complaining about a missing value in the "Polling Statement" field on the WCF-SQL adapter. – Leth Sep 25 '18 at 10:31
  • Hi i updated the polling statement in my answer try with the given example, and can you share your WCF-SQL config ?, once this adapter is connected and polls properly it will trigger the Webhttp adapter to fetch the data, and can be processed further. – Suraj Revankar Sep 25 '18 at 12:14
  • HI @Leth: I hope the question is answered with your updated question https://stackoverflow.com/questions/52511841/using-a-csv-to-xml-map-in-a-two-way-send-port-that-polls-data-from-a-public-rest with the above approach. You need to use a Custom pipeline matching your CSV schema with Flatfile Disassembler, from which you can map it to the XML, in the Inbound maps which would give you the desired result. – Suraj Revankar Sep 28 '18 at 10:43
  • Yes, sorry I forgot to mark the question as answered. I created a custom pipeline with a flatline disassembler using a premade schema. – Leth Sep 28 '18 at 11:19
  • Thanks, great. I hope you are getting the desired output with the pipeline followed by a map. – Suraj Revankar Sep 28 '18 at 11:25