Copying data from DB2 to Azure SQL Database using copy data activity and getting Invalid Parameter Error

Gaurav Agarwal 0

"errors": [ { "Code": 11402, "Message": "Failure happened on 'Source' side. ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property 'index' is invalid: 'Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name: index'.,Source=,''Type=System.ArgumentOutOfRangeException,Message=Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name: index,Source=mscorlib,'", "EventType": 0, "Category": 5, "Data": { "PropertyName": "index", "InvalidParameter": "Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name: index", "FailureInitiator": "Source" }, "MsgId": null, "ExceptionType": null, "Source": null, "StackTrace": null, "InnerEventInfos": [] } ],

Harishga 5,990 Reputation points Microsoft Vendor

2024-03-12T10:23:17.49+00:00
@Gaurav Agarwal

Welcome to Microsoft Q&A platform and thanks for posting your question here.

The error message suggests that there was a failure on the 'Source' side while copying data from DB2 to Azure SQL Database using the copy data activity. The error code is InvalidParameter, and the error message states that the value of the property 'index' is invalid. Specifically, the index was out of range, and it must be non-negative and less than the size of the collection. The parameter name is 'index'.

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

Check the source data in DB2 to ensure that it is valid and complete.

Verify that the copy activity is configured correctly, including the source and sink connections, and the mapping between the source and sink data.

Check the size of the collection and ensure that the index value is within the range of the collection.

If you still have the issue after trying the suggested steps, you can provide more information about the specific configuration and data being used in the copy activity to help diagnose the issue.

I hope this information helps you. Let me know if you have any further questions or concerns.
Harishga 5,990 Reputation points Microsoft Vendor

2024-03-13T08:29:59.5033333+00:00

@Gaurav Agarwal
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Gaurav Agarwal 0 Reputation points

2024-03-22T08:06:39.08+00:00

@Harishga

I have checked in more detail and it comes when the DB2 field having CLOB data and has large in length. Eg. LENGTH(<filed name>) > 300000.

In SQL to migrate CLOB data I am using VARCHAR(MAX)
Harishga 5,990 Reputation points Microsoft Vendor

2024-03-22T14:30:00.8766667+00:00
Hi @Gaurav Agarwal
To resolve this issue, you can try the following steps

Use the TEXT data type in Azure SQL Database instead of VARCHAR(MAX) to accommodate larger CLOB data. The TEXT data type can store up to 2^31-1 bytes of text data.

If using the TEXT data type is not an option, you can split the CLOB data into smaller chunks and migrate them separately using the copy data activity. You can use the SUBSTR function in DB2 to split the CLOB data into smaller chunks based on a specified length.

I hope this information helps you. Let me know if you have any further questions or concerns.
Cory Seaman 1 Reputation point

2024-08-07T21:00:36.26+00:00

@Gaurav Agarwal, were you able to get a resolution here, or determine the root cause? We're encountering the exact same issue, and guidance is very limited.

The suggestion regarding TEXT vs. VARCHAR(MAX)/VARBINARY(MAX) isn't correct, I don't think.

Share via

Copying data from DB2 to Azure SQL Database using copy data activity and getting Invalid Parameter Error

Your answer