Can ADF use a SQL generated correct JSON object and write directly to a Cosmos container with no mapping?

Question

Using SQL stored procs, I can generate legal JSON objects, many of which contain internal objects and arrays; sometime these are nested. I want to use these stored procs as the Source in an ADF pipeline Copy block.

How can I use ADF to write these complete JSON objects to a Cosmos container Sink?

Answer 1

Currently, Azure Data Factory (ADF) does not support writing SQL-generated JSON objects directly to a Cosmos DB container without any mapping. When you attempt to copy the results of a SQL query that generates JSON into a Cosmos DB for NoSQL container, the JSON structure may not be preserved as expected. Instead, the JSON may be treated as a string property in the document.

To work around this limitation, you can use a two-step process:

1. First Copy Activity: Use ADF to execute your SQL stored procedure to generate the JSON and write the output to an intermediary blob storage as a text file.
2. Second Copy Activity: Configure another copy activity to read from the blob storage and write the JSON data to the Cosmos DB container, ensuring that the JSON structure is preserved.

This approach allows you to effectively transfer your complete JSON objects, including nested structures, into the Cosmos DB container as intended.

Answer 2

Hi @Kurt von Nieda

Thanks for reaching to Microsoft QA, below are the few mitigation steps that may help you to address your reported issue -

ADF’s Copy Activity is the simplest and most efficient way to transfer data between your SQL Database and Cosmos DB. When setting up your sink (the destination), ADF can natively handle JSON objects—so you don’t need to define a schema or map fields manually.

In your Copy Activity source settings, connect to your SQL Database and select the stored procedure that returns your JSON output. Make sure the stored procedure emits properly formatted JSON so ADF can interpret it directly as dataset content.

Next, configure the sink to point to your Cosmos DB container. ADF can write raw JSON into Cosmos DB with no extra mapping required. Double-check your container’s partition key and connection details—it’s essential for correct data distribution and performance.

If your stored procedure outputs nested or complex JSON structures, ADF can still process them natively. That said, large or deeply nested arrays can impact performance, so consider breaking them up or testing the throughput limits if you’re dealing with big datasets.

Use ADF’s Debug feature to preview and validate your pipeline. This ensures the JSON structure is written correctly to Cosmos DB before you run full-scale operations.

Verify your connection strings and partition key setups.

Check for throttling or performance issues if you’re working with high data volumes.

Review pipeline logs in ADF’s Monitor tab for detailed error messages or warnings.

Once configured correctly, the process should smoothly move your JSON data from SQL to Cosmos DB without the need for manual mapping or schema definitions.

Here's a useful resource for further insights:

• Copy data to or from Azure Cosmos DB (SQL API) using Azure Data Factory
• Migrate from Azure SQL Database to Azure Cosmos DB

Hope this helps! Let me know if you have more questions or if there's something specific you're struggling with!