Unable to configure 'Copy data' activity for csv output from API - Marketo

[pmcmonag2]

Scenario:
I am trying to download a csv file in a pipeline but due to the schema of the response being csv (not json) a Copy Activity fails with the error:

Error:
Error occurred when deserializing source JSON file ''. Check if the data is in valid JSON object format.
Unexpected character encountered while parsing value: e. Path '', line 0, position 0.

I think this is due to the file in the response actually being csv format. In postman the csv delimited response returns fine. In the format:

Workarounds attempted:

1. Tried to add an accept header in the call in the Copy Data Activity. This does not work/allowed.

2. a Added a web activity to parse the response instead. Insepcting the output on running the pipeline looks like, where #### is the csv response including headers as in postman response.

{
"Response":{#######}
"ResponseHeaders": {
"Connection": "keep-alive",
"Date": "Thu, 13 Jun 2024 13:16:10 GMT",
"Server": "nginx",
"Content-Length": "917457",
"Content-Disposition": "attachment; filename=file",
"Content-Type": "text/csv; charset=UTF-8"
},
"executionDuration": 1
}

The issue here is there is no option in Fabric to add a sink for this output, or as in ADF created an inline dataset linked to blob storage. 

It is possible to add a setVariable activity and just get the .Response array which contains the csv, but as above it cannot be output anywhere. 

2 b) adding a new Copy Data Activity after the set variable there is no the option to have the input as a dynamic output (inline), the source must be a Fabric object:

Questions:

1. Can the Copy data activity be configured to take a csv response (rather than json)?

2. What is the Fabric alternative to creating a linked dataset to output a response or variable? (to output web activity response)

3. Can the Copy data activity be configured to accept output from an activity (like a variable)? (rather than a fabric entity like a warehouse/lakehouse)

[pmcmonag2]

Hi @v-cboorla-msft Support ticket now created - Case 2407100050001204 

Thanks

[v-cboorla-msft]

Hi @pmcmonag2 

Thanks for using Microsoft Fabric Community.

1. Can the Copy data activity be configured to take a csv response (rather than json)?

• Currently, the Microsoft Fabric Copy data activity is not directly configured to take a CSV response from a REST API call. While the Copy data activity can work with structured data formats like CSV (typically delimited text formats), it requires the data to be presented in a way it can parse. Directly interpreting a raw CSV response from a web activity might necessitate additional processing. However, it can handle delimited text files (like CSV) stored in Azure Blob Storage.

2. What is the Fabric alternative to creating a linked dataset to output a response or variable? (to output web activity response)

• In Microsoft Fabric, you can leverage the "For Each" activity to iterate through the array of responses obtained from the Web activity. Within this loop, you can utilize a nested "Copy data" activity to process each individual CSV response as a dataset. This approach allows you to handle the CSV data effectively. This approach allows you to treat the CSV data as a dataset within the Copy data activity.

3. Can the Copy data activity be configured to accept output from an activity (like a variable)? (rather than a fabric entity like a warehouse/lakehouse)

• It's important to note that the Copy data activity in Microsoft Fabric cannot directly accept output from an activity (like a variable) as its source. It requires a Fabric object (warehouse, lakehouse) as the input. Therefore, an intermediate approach like the "For Each" activity becomes necessary to process the CSV data retrieved from the Web activity.

I hope this information helps.

Thank you.

[pmcmonag2]

Thanks for the responses @v-cboorla-msft .

I still don't quite understand how the ForEach approach in 2) can be implemented, as doesn't the restriction you call out in 3) still apply? It's important to note that the Copy data activity in Microsoft Fabric cannot 

directly accept output from an activity (like a variable) as its source


The CopyData within the Foreach Loop still appears to require a Fabric object (lakehouse) as a source?

Perhaps easier to understand the output if I save off just the 'Response' off the call in a variable.

{ "name": "CSVResponse",
"value": "email,firstName,lastName,company,leadScore\npmcmcmonag@microsoft.com,Pat,McMonag,Microsoft,null\n
}

But this still makes it no easier to use CopyData within a ForEach?

[v-cboorla-msft]

Hi @pmcmonag2 

Apologies for the inconvenience and the delay in reponse.

Please reach out to our support team to gain deeper insights and explore potential solutions. It's highly recommended that you reach out to our support team. Their expertise will be invaluable in suggesting the most appropriate approach.

Please go ahead and raise a support ticket to reach our support team:

https://support.fabric.microsoft.com/support

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Thank you.

[v-cboorla-msft]

Hi @pmcmonag2 

We haven’t heard from you on the last response and was just checking back to see if you've had a chance to submit a support ticket. If you have, a reference to the ticket number would be greatly appreciated. This will allow us to track the progress of your request and ensure you receive the most efficient support possible.

Thank you.