Storing REST API Response as Raw JSON in SQL Table Instead of Mapped Columns in Azure Data Factory

Hi Shayan Siddique,
Yes, to store the complete REST API response as raw JSON in a single SQL column, you can use either of the following supported approaches:
Web Activity + Stored Procedure (Recommended for reliability)

Use Web Activity to call the REST API.

Capture full response using: @activity('WebActivityName').output.Response

Pass this output to a Stored Procedure Activity.

Insert into SQL table with a column like NVARCHAR(MAX).

This approach ensures the complete JSON response is preserved exactly as received from the API, without any schema mapping or automatic flattening by Azure Data Factory, and it is more reliable for handling large payloads, especially those exceeding 4 MB.
Copy Activity

In REST dataset → set JSON settings = Single document

In Sink (SQL) → create one column (NVARCHAR(MAX))

In Mapping tab → remove all mappings (important)

Let ADF perform default behavior (schema-agnostic copy)

In Azure Data Factory, arrays may still get flattened by default, so this approach works reliably only when the response is handled as a single JSON document and no tabular or column-level mapping is applied.

Make sure
Ensure column type is NVARCHAR(MAX)

For array responses → Copy Activity may still create multiple rows

For guaranteed single-row storage → Web Activity approach is preferred

For advanced scenarios → use Mapping Data Flow (Aggregate + collect())

To store REST API response as raw JSON in a single row, use Web Activity + Stored Procedure for full control and reliability. Copy Activity can work if configured as a single document with no mappings, but may not consistently handle array responses.
Reference Links:
Azure Data Factory REST connector
Azure Data Factory Web Activity

Hope this helps. If you have any follow-up questions, please let me know. I would be happy to help.

Please do not forget to "up-vote" wherever the information provided helps you, as this can be beneficial to other community members.

Use the REST connector in schema‑agnostic mode and store the full JSON text in a single SQL column.

Key points:

1. REST source: export JSON response as‑is
   • The REST connector supports exporting a REST API’s JSON response “as‑is” to a file‑based sink when no schema mapping is defined.
   • To get a single JSON payload instead of row/column mapping, do not define any mappings in the Copy activity Mapping tab.
   • This keeps the response as a JSON array like:

          [
            { <data object> },
            { <data object> },
            ...
          ]
     

2. Store JSON in SQL Server
   • JSON is textual, so it can be stored in an nvarchar (or native json type where supported) column in SQL Server.
   • Typical pattern:
     • Have a table such as:

              CREATE TABLE ApiRawResponse (
                  Id int IDENTITY(1,1) PRIMARY KEY,
                  ResponseJson nvarchar(max)
              );
       

     • Use ADF to write the entire JSON response into ResponseJson as a single value per row.
3. How to get “single row” behavior from ADF
   • ADF’s REST connector as source normally treats the JSON array as multiple rows when schema mapping is used.
   • To store the whole array in one row:
     • Keep REST source in default schema‑agnostic mode (no mapping).
     • Use an intermediate file‑based sink (for example, Blob/ADLS) that stores the response as a single JSON file.
     • Then use a second Copy activity (file → SQL) that reads the file as text and writes it into a single nvarchar(max) column.
   • The first copy preserves the raw JSON; the second copy treats the file contents as a single text value.
4. Processing later (optional)
   • Once stored, JSON can be parsed in SQL Server using OPENJSON, JSON_VALUE, etc., if needed to project into relational columns.

References:

• Copy and transform data from and to a REST endpoint by using Azure Data Factory
• JSON data in SQL Server