Copy Activity writing an empty value from REST API to SQL DW when preview is correct

Hi,

I have an copy activity to copy an value from an REST API to an SQL DW. I configured the source and I can even get a correct preview of the value and correct mapping (mapped the name of the value I wanted to the correct sink on the DW by using the import schema). But when I debug/trigger the pipeline the value written to to the DW is an empty string.

So, the copy is creating a new row that is not null and not writing the value from the REST API, even though the value is correct in the preview. What is happening?

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-06T07:39:34.013+00:00

Hi @Caio Henrique Segawa Tonetti ,

Welcome to Microsoft Q&A Platform. Thanks for posting the issue.

I faced this issue once and that is due to the incorrect mapping when I used parameters in dynamic mapping which is not throwing the error but we did not hear similar issues from other customers. Please recheck if any parameter values are used and the format of them. If issue persists, please share data factory name, region and pipeline run id to investigate further.

Looking forward for your response!
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-09T07:44:28.46+00:00

Hi @Caio Henrique Segawa Tonetti ,

We have not received a response from you. Are you still facing the issue? If you found a solution, would you please share it here with the community? Otherwise, let us know the details as requested and we will continue to engage with you on the issue.
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-10T12:38:00.15+00:00

Hi @Caio Henrique Segawa Tonetti ,

We still have not heard back from you. Following up to check if you found a solution, would you please share it here with the community? Otherwise, let us know the details as requested and we will continue to engage with you on the issue.
Caio Henrique Segawa Tonetti 1 Reputation point

2020-11-10T16:25:40.883+00:00

Hello!

I did not manage to solve this directly, but I did discover that I could send to a blob with a more simple
format and then read from there. But I couldn't manage to copy directly from the json I received from
a API.

For my personal research here, the ADF has some problems dealing with arrays inside the json fields, so
the format I wrote on the blob excluded every internal array field.

1 answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-12T08:26:33.333+00:00
Thanks @Caio Henrique Segawa Tonetti for sharing the approach used. One more point observed was to use casting to JSON when assigning parameter/previous activity output to dynamic mapping in copy data as below. It is to ensure that value is properly treated as JSON, please check if this helps in resolving using directly the API response in ADF.

@json(pipeline().parameters.testparam)
Please sign in to rate this answer.

1 person found this answer helpful.
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-16T07:03:22.147+00:00

Thanks @Caio Henrique Segawa Tonetti for upvoting the comment. Since it addressed the query, I have converted it to answer.

--------------

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

Want a reminder to come back and check responses? Here is how to subscribe to a notification.

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-17T08:41:37.243+00:00

Hi @Caio Henrique Segawa Tonetti ,

We have not received a response from you. Please suggest if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community member

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-20T08:32:11.67+00:00

Hi @Caio Henrique Segawa Tonetti ,

We still have not heard back from you. Following up to check if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Copy Activity writing an empty value from REST API to SQL DW when preview is correct

1 answer

Your answer