Mapping Address Values to Relative URLs in Azure Data Factory

Question

Hello,

I am working in ADF to post data to Business Central Temp table. I have set up my source dataset as reading from blob to REST as sink. I am having this issue where I have to send invoices to the respective temp tables in each company in Business Central. Each invoice has an address value which is included in the URL for example, the relative url for a company could be "company=DoralLink%20CA". Also what I have done is created a CSV file in the blob storage that has address value and relative url columns, I passed that in a look up activity. Now how can I parameterize that when the pipeline is run, it should look for the respective url, when the address value is read in the data flow based on the address value in the mapping dataset, so that in the Sink section where we have the relative url for the REST option, it should be sent to the right URL, which has each individual temp table. For example, I do not want to send Florida's invoices to Utah's temp table in Business Central. How can I do this?

Please advise

Answer 1

Hi @Kayode Ogidan
Welcome to Microsoft Q&A platform and thanks for posting your question here.

To send invoices to the correct temp tables in Business Central, you need to create a mapping of address values to relative URLs in a CSV file stored in blob storage. This mapping will help you determine the correct URL for each invoice based on its address value.

In your Azure Data Factory pipeline, you can use a Lookup activity to retrieve this mapping from the CSV file. The Lookup activity will read the CSV file and return the mapping as a dataset that you can use in your pipeline.

Next, you can use a Derived Column in your data flow to determine the correct relative URL for each invoice based on its address value. The Derived Column will use the mapping dataset returned by the Lookup activity to look up the correct relative URL for each invoice.

To dynamically construct the URL, you can create a new parameter in your REST sink dataset called RelativeUrl.. This parameter will be used to construct the URL dynamically based on the correct relative URL for each invoice. You can use this parameter in the dataset's connection properties to dynamically construct the URL.

Finally, in the Sink settings of your data flow, you can use dynamic content to reference the RelativeUrl.parameter and pass the correct URL to the REST sink dynamically. This will ensure that each invoice is sent to the correct temp table in Business Central.

Regarding the encoding of spaces as %20, the ADF automatically encodes spaces as %20 in URLs, so there's no need to manually encode them. it's still a good practice to double-check that spaces are correctly encoded in your CSV file or during URL construction in ADF to avoid any potential issues.

Reference
https://learn.microsoft.com/en-us/answers/questions/1083901/how-can-i-parameterize-rest-api-dataset-or-linked
https://stackoverflow.com/questions/63861942/where-to-specify-rest-api-parameter-for-get-method-in-adf-web-activity

 I hope this information helps you. Let me know if you have any further questions or concerns.