Calling REST API to fetch CSV data using Biztalk W

Question

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.

Answer 1

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:

3. 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.