👀 Views: 20 💬 Answers: 1 📅 Created: 2025-06-08

azure-data-factory lookup json error SQL

I've been researching this but I've encountered a strange issue with I'm currently using Azure Data Factory (ADF) version 2 to orchestrate data movement from an Azure SQL Database to an Azure Blob Storage... I've set up a pipeline that includes a Lookup activity to retrieve data from the SQL database before passing it to a Copy activity. However, I'm running into an scenario where the pipeline fails with the behavior: `Invalid JSON format.` I've double-checked the query being executed in the Lookup activity, and it seems correct. Here's the query I'm using: ```sql SELECT * FROM dbo.MyTable WHERE IsActive = 1; ``` The Lookup activity is configured to retrieve the first row, and the output should ideally be a valid JSON object. I've tried printing the output to the debug console, and it looks like this: ```json { "value": [{ "ID": 1, "Name": "Sample Item", "IsActive": true }] } ``` Despite this, the pipeline fails every time I run it. I've also tried modifying the JSON structure in various ways, but nothing seems to resolve this scenario. To troubleshoot, I set the 'First row only' option in the Lookup activity, but I still encounter the same behavior. I have confirmed that the JSON output from the Lookup activity is being passed correctly to the Copy activity, but it appears that ADF is expecting a different format. Has anyone encountered a similar scenario with Lookups in Azure Data Factory, and how did you resolve the 'Invalid JSON format' behavior? Any suggestions on adjustments I might make to either the query or the activity configuration would be greatly appreciated! I've been using Sql for about a year now. Could someone point me to the right documentation? This issue appeared after updating to Sql 3.9. I appreciate any insights!