Trying to copy for JSON API result to SQL*Server table, getting error on any data type I offer in the Sink

Question

Trying to copy for JSON API result to SQL*Server table, getting error on any data type I offer in the Sink

John Edwards 35

I am pulling a JSON result from an API, Azure Synapse via a Copy command. Data is coming in fine, and I can copy to the table if I do it via a temporary file in the middle. But I don't want to do that due to concurrency, cleanup, etc. I want to go from the JSON source to an Azure SQL sink. Three simple fields, all three simple text. I get the following error --

{ "errorCode": "2200", "message": "ErrorCode=DataTypeNotSupported,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column: Agent_Name,The data type text is not supported for the column named Agent_Name.,Source=,'", "failureType": "UserError", "target": "Copy_Users", "details": [] }

I've offered nchar, varchar, text . . . I get the exact same error just with a different data type shown in the error string. Null/Not Null doesn't seem to matter . . .

Any suggestions on what to try?

Himaja Y 375 Reputation points Microsoft External Staff Moderator

2026-08-17T17:56:25.6666667+00:00
Hi John Edwards,

Thank you for the details provided.

The error indicates a data type or schema mapping issue with the Agent_Name column.

Please try the following:

In Copy Activity → Mapping, remove the existing mapping and select Import schemas again.

Verify that Agent_Name is returned as a simple string value in the JSON response.

Check whether Agent_Name is sometimes returned as null, an object, or an array instead of a string.

In the Azure SQL sink, use VARCHAR or NVARCHAR for Agent_Name rather than the legacy TEXT type.

If Agent_Name is a nested JSON field, explicitly map/extract the required value before writing it to Azure SQL.

Since the data can be written successfully to a temporary file, the API response itself may be valid. The issue is more likely occurring during the direct JSON-to-Azure SQL schema/type mapping.

If the issue persists, please share the sample JSON response, Azure SQL table definition, and Copy Activity Mapping configuration so we can identify the exact mismatch.

Reference:

https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping
John Edwards 35 Reputation points

2026-08-17T17:58:58.6966667+00:00

I've had to do that multiple times to capture the new format of the field each time I changed it in Azure SQL. Doesn't fix the problem. And the problem applies to all fields. If I delete the Agent_Name mapping it moves on to the next field in the list.
Thanks for responding though. It's nice to know someone is out there.

3 answers

Your answer

Himaja Y 375 Reputation points Microsoft External Staff Moderator

2026-08-17T17:56:25.6666667+00:00

Hi John Edwards,

Thank you for the details provided.

The error indicates a data type or schema mapping issue with the Agent_Name column.

Please try the following:

In Copy Activity → Mapping, remove the existing mapping and select Import schemas again.

Verify that Agent_Name is returned as a simple string value in the JSON response.

Check whether Agent_Name is sometimes returned as null, an object, or an array instead of a string.

In the Azure SQL sink, use VARCHAR or NVARCHAR for Agent_Name rather than the legacy TEXT type.

If Agent_Name is a nested JSON field, explicitly map/extract the required value before writing it to Azure SQL.

Since the data can be written successfully to a temporary file, the API response itself may be valid. The issue is more likely occurring during the direct JSON-to-Azure SQL schema/type mapping.

If the issue persists, please share the sample JSON response, Azure SQL table definition, and Copy Activity Mapping configuration so we can identify the exact mismatch.

Reference:

https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping
John Edwards 35 Reputation points

2026-08-17T17:58:58.6966667+00:00

I've had to do that multiple times to capture the new format of the field each time I changed it in Azure SQL. Doesn't fix the problem. And the problem applies to all fields. If I delete the Agent_Name mapping it moves on to the next field in the list.
Thanks for responding though. It's nice to know someone is out there.

Answer 1

Hello @John Edwards

That follow-up changes the diagnosis. If removing Agent_Name simply causes the next mapped field to fail with the same 2200 / DataTypeNotSupported error, I wouldn't keep changing the Azure SQL column types. The problem is probably occurring before the data reaches SQL.

Since your source is a REST API returning JSON, Synapse Copy Activity treats it as a hierarchical source. Microsoft documents that REST JSON needs hierarchical-to-tabular schema mapping when writing into a relational sink such as Azure SQL.

The fact that the response copies successfully to a temporary file also doesn't prove that it has been converted into tabular scalar values. The REST connector can copy a JSON response as-is to a file without schema mapping.

I'd inspect the raw API response next. In particular, determine whether it looks like:

{
  "value": [
    {
      "Agent_Name": "John",
      "Field2": "abc",
      "Field3": "xyz"
    }
  ]
}

If your records are inside an array such as value, configure that array as the collection reference and map the individual properties from each array element to the SQL columns. Microsoft specifically documents collectionReference for converting objects within a JSON array into individual tabular rows.

For example, the mapping concept would be:

Collection reference: $['value']

['Agent_Name'] -> Agent_Name

['Field2'] -> Field2

['Field3'] -> Field3

Don't specify text, varchar, etc. as source types just to make the mapping work. Those SQL data types won't solve the problem if Synapse still sees the source value as a JSON object/array rather than a scalar string.

I'd therefore ask you to post a sanitized sample of the actual JSON response, particularly the root object and first record, along with the current Copy Activity Mapping configuration. That should tell us whether the missing/incorrect collectionReference or JSON paths are the real issue.

If the JSON contains more complex nested arrays/objects that can't be flattened cleanly with Copy Activity, Microsoft recommends Mapping Data Flow for more advanced hierarchical-to-tabular transformations.

Sharing you these references:

Microsoft Learn – Schema and data type mapping in Copy Activity | https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping?

Microsoft Learn – REST connector for Azure Data Factory and Synapse | https://learn.microsoft.com/en-us/azure/data-factory/connector-rest?

Please "Accept the Answer" if this information helped you. This will help us and others in the community as well.

John Edwards 35 Reputation points

2026-08-17T20:31:29.06+00:00

Yeah, I can't convince the interface to do what I want it to do. I got it to work pulling from the file, got the hierarchy, got the types assigned to the source fields. And I know that part of it is that you have to build half of it, commit, close, and then reopen it to get them to stick for further work. But I can't duplicate it. I'm going to need to do about 30 of these, so I need to fully understand how to reproduce this.
Allan Solomon Mejia 7,915 Reputation points

2026-08-17T21:05:49.21+00:00

Hello @John Edwards

Yes, that behavior makes sense. The Copy Activity mapping UI can be a little unintuitive with hierarchical JSON, especially when you need to reproduce the same mapping across many activities.

Since you were able to get the hierarchy and data types working, I wouldn't recommend manually rebuilding this 30 times.

Once you have one Copy Activity configured correctly, open the pipeline's Code/JSON view and inspect the Copy Activity's translator / mappings configuration. That mapping is stored as part of the activity definition, so it can be used as the reference for reproducing the configuration rather than relying entirely on the visual mapping interface.

You can also parameterize the pipeline/dataset where the structure is the same and only the source, destination, or other values change. That would be much easier to maintain than creating 30 independently configured mappings.

If the 30 files have significantly different hierarchical structures, though, then we may need a different approach, potentially Mapping Data Flow or programmatically generated mappings.

If you can share the working Copy Activity JSON, particularly the translator/mappings section, I can help identify exactly what the UI generated and show how to reproduce it consistently.
Allan Solomon Mejia 7,915 Reputation points

2026-08-17T21:55:29.24+00:00

Hello @John Edwards

Please let me know if you need any further assistance. Otherwise, if the information provided resolved your issue, please click “Accept Answer” to help others who may encounter the same problem.

Thank you!

Answer 2

Alright, I got this to run, but it's not pretty.Long story short -- I can't reproduce the successful field mapping that I can create super-easily by using a text file on the disk.

When using a text file on the disk with the same json material in it, I can import the schema, and the tool creates the hierarchy and grabs the field names and sets everything up for you to just pick what you want. I'm able to assign the fields as I like, the mapping understands exactly what the source I'm talking about it and assigns it. The heart of the problem hasn't been the format of anything, it's been the thing at the very end of the error message -- Source=, There's no source fields when I create the connection by hand. They appear on the screen, but they aren't in the json that's behind it in a proper structure. The front-end isn't working correctly.

So I did this --

{B8D84954-8010-493A-B1CC-00E77E78F897}

I created two versions of the same thing, one that pulls directly from the API (on top) and a second that pulls from a sample output that I sent into a file on my drive (on the bottom). I used the second to import schemas, connected the fields I needed, and tested until it was right. File/Save.

Then I went into the json, and copied the fields part of the code from one activity into the other --

"mappings": [

{

``"source": {

``"path": "['id']"

},

``"sink": {

``"name": "ID",

``"type": "String"

}

},

{

``"source": {

``"path": "['name']"

},

``"sink": {

``"name": "Agent_Name",

``"type": "String"

}

},

{

``"source": {

``"path": "['title']"

},

``"sink": {

``"name": "Title",

``"type": "String"

}

],

Not pretty, but repeatable, dependable, teachable, verifiable. I've spent enough time ironing all this out and it's not my job to fix the interface's shortcomings.

Thank you both! Each of you got me looking at the right things!

Answer 3

Alright, I got this to run, but it's not pretty. Long story short -- I can't reproduce the successful field mapping via the API source that I can create super-easily by using a text file on the disk as a source.

When using a text file source on the disk with the same json material in it, I can import the schema, and the tool creates the hierarchy and grabs the field names and sets everything up for you to just pick what you want. I'm able to assign the fields as I like, and the mapping understands exactly what the source I'm talking about is assigns it properly. The heart of the problem hasn't been the format of anything. It's been the detail at the very end of the error message -- Source=, There's no API connection source fields attached to the mapping when I create the connections by hand. Fields appear on the screen, but they aren't linked to fields from the Source in the json that's behind it in a proper structure. The json simple doesn't show those mappings at all. The front-end editor isn't working correctly on the mapping page.

So I did this --

{B8D84954-8010-493A-B1CC-00E77E78F897}

I created two versions of the same Copy activity, one that pulls directly from the API (on top) and a second that pulls from a sample output that I copied into a file on my drive (on the bottom). I used that second file-based Source to import schemas, then connected the fields I needed, then tested until it was clean and running correctly. File/Save.

Then I went into the json for the unit and copied the fields part of the code from the file-based activity into the API-based activity --

"mappings": [

{

``"source": {

``"path": "['id']"

},

``"sink": {

``"name": "ID",

``"type": "String"

}

},

{

``"source": {

``"path": "['name']"

},

``"sink": {

``"name": "Agent_Name",

``"type": "String"

}

},

{

``"source": {

``"path": "['title']"

},

``"sink": {

``"name": "Title",

``"type": "String"

}

],

After the hand-copy of the source code, I saved the pipe. Closed it. Reopened it. Ran it. Success.

Not pretty, but repeatable, dependable, teachable, verifiable. I've spent enough time ironing all this out and it's not my job to fix the interface's shortcomings. I have a path to solution.

Thank you both! Each of you got me looking at the right things!