Synapse Copy data activity POST issue

Question

Synapse Copy data activity POST issue

Giri, Seshu 26

I am using Copy data activity to call an API which returns a string. I am using POST method with 'Request Body'. I am getting error code 2200 and here is the request body. Please see the screen print and let me know how to fix it.

{  
"Variables": [  
    {  
        "VARName": "EmpCode",  
        "VARValue": "123"  
    }  
]  
}

Error I am getting

{  
    "errorCode": "2200",  
    "message": "Failure happened on 'Source' side. ErrorCode=JsonInvalidDataFormat,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error occurred when deserializing source JSON file ''. Check if the data is in valid JSON object format.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Newtonsoft.Json.JsonReaderException,Message=Unexpected character encountered while parsing value: <. Path '', line 0, position 0.,Source=Newtonsoft.Json,'",  
    "failureType": "UserError",  
    "target": "Copy data1",  
    "details": []  
}

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T00:08:13.087+00:00

Hello @Giri, Seshu ,

Welcome to the MS Q&A platform.

Are you Performing explicit mapping (Import Schemas in Mapping section of Copy) or Auto mapping from source to sink?

If you are Performing auto mapping, could you please try explicit mapping by clicking import schemas in the Mapping section of your copy activity?
Giri, Seshu 26 Reputation points

2022-09-08T00:15:04.563+00:00

Hi @Bhargava-MSFT

Thanks for your response. The error message shows "Failure happened on 'Source' side". Do you think it is related to the mapping? Let me try to get some screenprints.

Giri, Seshu 26

@Bhargava-MSFT

I tried this GET method in 2 ways

Copy data activity using REST connector
Copy data activity using HTTP connector (per microsoft, if you are downloading file using HTTP connector)

Pipeline Run ID: 85d367e5-a8f3-4fdd-a6ad-318e4d58e055

Screen print one shows Mapping part of Copy data (REST)

I think, by using HTTP connector, I think the call is API successful (not sure). I see different error in this case. We already have 'Storage Blob Container' role and from a Web activity we are able to read a file in the storage account.

{  
    "errorCode": "2200",  
    "message": "ErrorCode=AdlsGen2OperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ADLS Gen2 operation failed for: An error occurred while sending the request.. Account: 'mystorageaccount'. FileSystem: 'synapseuat'..,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Net.Http.HttpRequestException,Message=An error occurred while sending the request.,Source=mscorlib,''Type=System.Net.WebException,Message=The remote name could not be resolved: 'mystorageaccount.dfs.core.windows.net',Source=System,'",  
    "failureType": "UserError",  
    "target": "Copy data2",  
    "details": []  
}

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T18:45:36.257+00:00

Hello @Giri, Seshu ,

It looks like some issue with the storage account.

Can you please check if the firewall is enabled on the storage account?

Go to Azure Portal -> Storage Account -> Networking -> Check Allow Access From All Networks or selected Vnet and IPaddress.
If it is enabled - It means the storage account is firewall enabled.
Giri, Seshu 26 Reputation points

2022-09-08T18:50:25.853+00:00

@Bhargava-MSFT

Current setting is 'Enabled from Selected virtual networks and IP addresses", but both storage account and Synapse under same resource group.

Anything else needed to checked?
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T18:53:40.167+00:00

Hello @Giri, Seshu ,
Thank you for the reply. I am checking with my team further on this. I will get back to you at the earliest
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T19:07:52.277+00:00

Hello @Giri, Seshu ,

Could you please confirm, if you were able to successfully test the storage account linked service connection(from the copy activity at the sink end)?
Giri, Seshu 26 Reputation points

2022-09-08T19:30:33.18+00:00

Yes. See below screen prints
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T20:11:57.403+00:00

Hi @Giri, Seshu ,

I see you are using the managed virtual Network Azure IR for your sink,

The issue is most likely with networking on the IR. Are you using the auto IR or the SHIR on the source end?

When a copy activity uses different IRs for Source/Sink, only 1 IR will be used for transfer. The precedence is as follows:

Self-Hosted IR > Azure IR with Managed VNET > Autoresolve IR

Ex: If copy activity Source use Self Hosted(SHIR) and Sink use Managed VNET, at runtime only SHIR will be used. This means that SHIR will connect to both Source and Sink.

In this case, we need to whitelist SHIR IP addresses.
Giri, Seshu 26 Reputation points

2022-09-08T20:24:31.133+00:00
@Bhargava-MSFT

Excellent point. Source is using SHIR and Sink is using Auto IR. I just added SHIR IP address to Storage account networking --> Firewall and retried and still got same error.

=The remote name could not be resolved: 'mystorageaccount.dfs.core.windows.net',Source=System,

Do you think it takes sometime to propagate ?

or

Do you want me to create new 'Linked Service' to Gen2 using SHIR?
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T20:37:22.773+00:00

@Giri, Seshu ,
I believe it will take a min or 2. But if it didn't work, can you please test this with the new 'Linked Service' to Gen2 using SHIR and see if it works?
Giri, Seshu 26 Reputation points

2022-09-08T20:52:39.143+00:00
@Bhargava-MSFT

I just tried to create new 'Linked Service' using SHIR and it says connection failed with same error. Below are couple of things I looked at.

Synapse Workspace has permission to Storage account. Storage Blob Data Contributor role and Storage File Data SMB Share Contributor See screen print

My login and also I am part of role (name: AzSynapseAdmin). Both of those

Synapse Workspace access to Storage account

My ID and AzSynapseAdmin roles
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T21:12:25.877+00:00

Hi @Giri, Seshu ,

Synapse MSI needs to have "Storage blob data contributor" access on the Gen2 account (you have already confirmed that your synapse has access)

also on the workspace level, can you please provide "synapse administrator" role for your Synapse MSI as well as to your User ID?
Giri, Seshu 26 Reputation points

2022-09-08T21:26:25.507+00:00

@Bhargava-MSFT

We already have that, see below
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T22:07:16.13+00:00

Hello @Giri, Seshu ,
Thank you for the confirmation. Let me do some research and get back to you.
Giri, Seshu 26 Reputation points

2022-09-13T12:58:02.67+00:00

@Bhargava-MSFT

Sorry for the delay! this problem is solved now. Thanks.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-13T16:28:09.613+00:00

Hello @Giri, Seshu ,
Glad to know that your issue is resolved.
It would be great if you could share the resolution here so that it can be helpful to others.

1 answer

Your answer

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T00:08:13.087+00:00

Hello @Giri, Seshu ,

Welcome to the MS Q&A platform.

Are you Performing explicit mapping (Import Schemas in Mapping section of Copy) or Auto mapping from source to sink?

If you are Performing auto mapping, could you please try explicit mapping by clicking import schemas in the Mapping section of your copy activity?
Giri, Seshu 26 Reputation points

2022-09-08T00:15:04.563+00:00

Hi @Bhargava-MSFT

Thanks for your response. The error message shows "Failure happened on 'Source' side". Do you think it is related to the mapping? Let me try to get some screenprints.
Giri, Seshu 26 Reputation points

2022-09-08T00:40:18.453+00:00

@Bhargava-MSFT

I tried this GET method in 2 ways

Copy data activity using REST connector

Copy data activity using HTTP connector (per microsoft, if you are downloading file using HTTP connector)

Pipeline Run ID: 85d367e5-a8f3-4fdd-a6ad-318e4d58e055

Screen print one shows Mapping part of Copy data (REST)

I think, by using HTTP connector, I think the call is API successful (not sure). I see different error in this case. We already have 'Storage Blob Container' role and from a Web activity we are able to read a file in the storage account.

{ "errorCode": "2200", "message": "ErrorCode=AdlsGen2OperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ADLS Gen2 operation failed for: An error occurred while sending the request.. Account: 'mystorageaccount'. FileSystem: 'synapseuat'..,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Net.Http.HttpRequestException,Message=An error occurred while sending the request.,Source=mscorlib,''Type=System.Net.WebException,Message=The remote name could not be resolved: 'mystorageaccount.dfs.core.windows.net',Source=System,'", "failureType": "UserError", "target": "Copy data2", "details": [] }
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T18:45:36.257+00:00

Hello @Giri, Seshu ,

It looks like some issue with the storage account.

Can you please check if the firewall is enabled on the storage account?

Go to Azure Portal -> Storage Account -> Networking -> Check Allow Access From All Networks or selected Vnet and IPaddress.
If it is enabled - It means the storage account is firewall enabled.
Giri, Seshu 26 Reputation points

2022-09-08T18:50:25.853+00:00

@Bhargava-MSFT

Current setting is 'Enabled from Selected virtual networks and IP addresses", but both storage account and Synapse under same resource group.

Anything else needed to checked?
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T18:53:40.167+00:00

Hello @Giri, Seshu ,
Thank you for the reply. I am checking with my team further on this. I will get back to you at the earliest
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T19:07:52.277+00:00

Hello @Giri, Seshu ,

Could you please confirm, if you were able to successfully test the storage account linked service connection(from the copy activity at the sink end)?
Giri, Seshu 26 Reputation points

2022-09-08T19:30:33.18+00:00

Yes. See below screen prints
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T20:11:57.403+00:00

Hi @Giri, Seshu ,

I see you are using the managed virtual Network Azure IR for your sink,

The issue is most likely with networking on the IR. Are you using the auto IR or the SHIR on the source end?

When a copy activity uses different IRs for Source/Sink, only 1 IR will be used for transfer. The precedence is as follows:

Self-Hosted IR > Azure IR with Managed VNET > Autoresolve IR

Ex: If copy activity Source use Self Hosted(SHIR) and Sink use Managed VNET, at runtime only SHIR will be used. This means that SHIR will connect to both Source and Sink.

In this case, we need to whitelist SHIR IP addresses.
Giri, Seshu 26 Reputation points

2022-09-08T20:24:31.133+00:00

@Bhargava-MSFT

Excellent point. Source is using SHIR and Sink is using Auto IR. I just added SHIR IP address to Storage account networking --> Firewall and retried and still got same error.

=The remote name could not be resolved: 'mystorageaccount.dfs.core.windows.net',Source=System,

Do you think it takes sometime to propagate ?

or

Do you want me to create new 'Linked Service' to Gen2 using SHIR?
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T20:37:22.773+00:00

@Giri, Seshu ,
I believe it will take a min or 2. But if it didn't work, can you please test this with the new 'Linked Service' to Gen2 using SHIR and see if it works?
Giri, Seshu 26 Reputation points

2022-09-08T20:52:39.143+00:00

@Bhargava-MSFT

I just tried to create new 'Linked Service' using SHIR and it says connection failed with same error. Below are couple of things I looked at.

Synapse Workspace has permission to Storage account. Storage Blob Data Contributor role and Storage File Data SMB Share Contributor See screen print

My login and also I am part of role (name: AzSynapseAdmin). Both of those

Synapse Workspace access to Storage account

My ID and AzSynapseAdmin roles
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T21:12:25.877+00:00

Hi @Giri, Seshu ,

Synapse MSI needs to have "Storage blob data contributor" access on the Gen2 account (you have already confirmed that your synapse has access)

also on the workspace level, can you please provide "synapse administrator" role for your Synapse MSI as well as to your User ID?
Giri, Seshu 26 Reputation points

2022-09-08T21:26:25.507+00:00

@Bhargava-MSFT

We already have that, see below
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T22:07:16.13+00:00

Hello @Giri, Seshu ,
Thank you for the confirmation. Let me do some research and get back to you.
Giri, Seshu 26 Reputation points

2022-09-13T12:58:02.67+00:00

@Bhargava-MSFT

Sorry for the delay! this problem is solved now. Thanks.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-13T16:28:09.613+00:00

Hello @Giri, Seshu ,
Glad to know that your issue is resolved.
It would be great if you could share the resolution here so that it can be helpful to others.

Answer 1

Bhargava-MSFT 31,361 Microsoft Employee Moderator

Hello @Giri, Seshu ,

Per my research, this error occurs when the storage account name is incorrect. Can you please validate the account name from your end again?

Error:
The remote name could not be resolved: 'mystorageaccount.dfs.core.windows.net',Source=System,

Share via

Synapse Copy data activity POST issue

1 answer

Your answer