CSV format not retained in Lookup - Azure Data factory

Question

Hi experts,

I am trying to call D365FO Recurrence job in my pipeline.

I am facing the issue in the format of the data sent from Lookup activity.

My lookup activity has the source data set as CSV file , in my case a customer group file. (Seperated by commas , first row as header).

The output value of the lookup activity is

The web activity inside the for each loop in which I am calling the recurrence job configured in D365FO for import. The data is expected in the format of CSV file like

Though the job in D365FO receives the data, it ends up in error with this format sent in the body

Is there any alternate approach to send the data in the exacr csv format?

Answer 1

Hi @Anonymous ,

There is a workaround you can do:

1. Create a Dataset with Column Delimiter --> Tab(\t) and unselect First row as header
2. In the lookupActivity, Skip line count --> 1
3. In the Foreach, Setting --> Items --> @activity('Lookup1').output.value
4. In the Web Activity, Body --> @item().Prop_0

Please see the screenshots for details. Hope this helps! Thanks!

----------

If the above response is helpful, please accept as answer and upvote it. Thanks!

Answer 2

Thanks much Nasreen.

I did the same earlier, but i tried as '@activity('Lookup1').output.value.Prop_1'.
And this was not giving me the result.

Your tips helped me to address this issue.

Thanks again.

Answer 3

Hi @Nasreen Akter ,

I am facing the same issue again.

I notice the format is lost in CSV when the data is copied from SQL.

I am trying to copy the data from Azure SQL to CSV and the above format is not retained during copy.

The preview data shows

Is there any way the format can be retained when copied from sql? Or do i need to tune my approach?