Error when loading SQL Server Views into JSON files during COPY DATA activity

Question

I am copying data from SQL Server to Azure Blob Storage as a JSON file with gzip compression using a COPY DATA activity within a ForEach activity. However, if the table from the lookup activity is actually a view in SQL Server, I get an error "Index was out of range. Must be non-negative and less than the size of the collection." when trying to run the pipeline. The pipeline succeeds if I output as a CSV but I need it to be in JSON format. I have attached the configurations for my COPY DATA Source and Sink. Can anyone help me resolve this issue?

ErrorCode=UserErrorFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The file operation is failed, upload file failed at path: 'snowflakestaging/XXXX/dbo.XXXX.gz'.,Source=Microsoft.DataTransfer.Common,''Type=System.ArgumentOutOfRangeException,Message=Index was out of range. Must be non-negative and less than the size of the collection.

Parameter name: index,Source=mscorlib,'

Source

Sink

Answer 1

Hi Jun Hao Goh,

Thanks for reaching out to Microsoft Q&A.

Please check this. You issue is similar except for the source is a Snowflake.

https://stackoverflow.com/questions/62915615/index-out-of-range-error-when-creating-snowflake-linked-service-in-azure-data-fa

Check and let me know.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Answer 2

Hello @Jun Hao Goh

Based on the error message you provided, it seems like there is an issue with the file operation when uploading the file to the specified path.

The error message also indicates that the index is out of range, which could be related to the JSON file format. One thing to note is that when copying data from SQL Server to Azure Blob Storage as a JSON file with gzip compression, the data needs to be properly formatted as JSON.

If the table from the lookup activity is actually a view in SQL Server, it's possible that the data is not properly formatted as JSON, which could be causing the issue.

To resolve this issue, you can try the following steps:

1. Check the data in the view to ensure that it is properly formatted as JSON. You can use a tool like JSONLint to validate the JSON format.
2. If the data is not properly formatted as JSON, you can modify the view in SQL Server to ensure that it outputs properly formatted JSON. You can use the FOR JSON clause in SQL Server to format the data as JSON.
3. If the data is properly formatted as JSON, you can try outputting the data as a CSV file instead of a JSON file to see if the pipeline succeeds. If the pipeline succeeds with a CSV file, there may be an issue with the JSON file format.
4. If the pipeline still fails with a CSV file, you can try modifying the configurations for your COPY DATA Source and Sink to ensure that they are properly configured. You can refer to the Azure documentation for more information on configuring the COPY DATA activity.

I hope this helps further.

---

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.