ADF - Index out of range

Question

ADF - Index out of range

Alex Fillingham 1

hello,

we're getting the following error in ADF when pulling data from the source side.

"An error happened while reading data from the provider: 'Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index"

we're essentially running a "select * from (table)".

Can anyone shed a light on why this may now be happening? The loads have been working fine previously and now seems to be cropping up more and more on this source / linked service (sybase).

Cheers

KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-03-12T15:59:51.027+00:00

Hi @Alex Fillingham ,

Thanks for reaching out. My understanding is that that pipeline which used to work has suddenly started throwing the above mentioned error. Please correct if I misunderstood your query.
Also please do confirm if you have made any changes recently to the pipeline (which was working fine previously) or the linked service connection to Sybase? Or is this a new pipeline that you are developing? Also please do check if this is happening while querying on a specific table or consistent with all other tables? If it is consistent with all other tables then it could be an issue related to connector.

Please share the failed pipeline runID and activity runID so that we can check with internal team.

We look forward to your response.

Thank you.
Alex Fillingham 1 Reputation point

2021-03-12T16:07:43.7+00:00

hello @KranthiPakala-MSFT thanks for reaching out.

This is a pipeline that has been in production for a good while now and we haven't had to amend it.

The error has been happening on a couple of specific tables (a few months ago) and has now gone to other tables but not all in the extract are affected (bizarrely).

Pipeline Run ID - 7752dfa9-8328-47b5-a0a8-3c0ee995ba76
Activity Run ID - 84b38673-07af-4c4e-8e1c-e1fe4f00a14a

These are from the load last night.

Please let me know if you need anything else from me.

Thanks
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-03-15T20:26:55.393+00:00

Hi @Alex Fillingham ,

Thanks for the response and details. We will look into the runID's and will get back to you soon.

Thank you for your patience
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-03-17T20:16:03.563+00:00

Hi @Alex Fillingham ,

Just wanted to let you know that, I have escalated this issue to internal team and awaiting response. Will keep you posted as soon as I hear back from the team.

Sorry for the delay and we appreciate your patience.

Thank you
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-03-23T23:50:47.333+00:00

Hi @Alex Fillingham ,

Sorry for the delay in response. Could you please confirm whether the table queried is being frequently updated by other application as well, during the pipeline running phase? From the backend log, the issue only happened against the table with the prefix AutoMatchPerson. If yes, could you please stop other applications and see whether this issue is gone? Further if the issue persists, to quickly mitigate, product team has recommended to use ODBC connector to see whether there is similar issue with that connector too.

And if you notice the same issue, please let us know.

We look forward to your confirmation.

Thanks
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-03-25T15:19:12.757+00:00

Hi @Alex Fillingham ,

Following up to see if you have got a chance to try the suggestion provided in my previous comment? Do let us know how it goes.

Thank you
Alex Fillingham 1 Reputation point

2021-03-25T15:46:48.537+00:00

Hello @KranthiPakala-MSFT ,

Sorry for the delay I had seen your message.

The table outlined above is a very "heavy" transactional table at source.

We found the error across multiple tables, we think its because the ADF is assigning the numeric fields the equivalent of a "small int" data type and then when the numeric value exceeds that length we then get "out of range".

We also think this because we've narrowed it down further to only numeric columns where we have this problem and if we explicitly cast / convert them to a larger numerical format, we no longer get the problem.

If I'm right in thinking, ADF will analyze the top X rows and give a suggested data type?

As for the ODBC suggestion, this is how we connect to this source for our on prem extracts and is something we're considering here also moving forwards.

Cheers
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-04-05T23:43:30.437+00:00

Hi @Alex Fillingham ,

Sorry for the delay. I am waiting for a response form product team about your query. In the meantime could you please confirm if you have got a chance to try with ODBC connector and see the behavior? Do let us know if you notice any change in the behavior.

Thank you
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-04-07T17:06:08.707+00:00

Hi there,

Just checking to see if you have got a chance to try with ODBC connector and any change in behavior?

Thanks
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-04-09T16:17:26.387+00:00

Hi there,

We still have not heard back from you. Just wanted to check if you have got a chance to try with ODBC connector and any change in behavior?

Thanks

KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-03-12T15:59:51.027+00:00

Hi @Alex Fillingham ,

Thanks for reaching out. My understanding is that that pipeline which used to work has suddenly started throwing the above mentioned error. Please correct if I misunderstood your query.
Also please do confirm if you have made any changes recently to the pipeline (which was working fine previously) or the linked service connection to Sybase? Or is this a new pipeline that you are developing? Also please do check if this is happening while querying on a specific table or consistent with all other tables? If it is consistent with all other tables then it could be an issue related to connector.

Please share the failed pipeline runID and activity runID so that we can check with internal team.

We look forward to your response.

Thank you.
Alex Fillingham 1 Reputation point

2021-03-12T16:07:43.7+00:00

hello @KranthiPakala-MSFT thanks for reaching out.

This is a pipeline that has been in production for a good while now and we haven't had to amend it.

The error has been happening on a couple of specific tables (a few months ago) and has now gone to other tables but not all in the extract are affected (bizarrely).

Pipeline Run ID - 7752dfa9-8328-47b5-a0a8-3c0ee995ba76
Activity Run ID - 84b38673-07af-4c4e-8e1c-e1fe4f00a14a

These are from the load last night.

Please let me know if you need anything else from me.

Thanks
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-03-15T20:26:55.393+00:00

Hi @Alex Fillingham ,

Thanks for the response and details. We will look into the runID's and will get back to you soon.

Thank you for your patience
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-03-17T20:16:03.563+00:00

Hi @Alex Fillingham ,

Just wanted to let you know that, I have escalated this issue to internal team and awaiting response. Will keep you posted as soon as I hear back from the team.

Sorry for the delay and we appreciate your patience.

Thank you
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-03-23T23:50:47.333+00:00

Hi @Alex Fillingham ,

Sorry for the delay in response. Could you please confirm whether the table queried is being frequently updated by other application as well, during the pipeline running phase? From the backend log, the issue only happened against the table with the prefix AutoMatchPerson. If yes, could you please stop other applications and see whether this issue is gone? Further if the issue persists, to quickly mitigate, product team has recommended to use ODBC connector to see whether there is similar issue with that connector too.

And if you notice the same issue, please let us know.

We look forward to your confirmation.

Thanks
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-03-25T15:19:12.757+00:00

Hi @Alex Fillingham ,

Following up to see if you have got a chance to try the suggestion provided in my previous comment? Do let us know how it goes.

Thank you
Alex Fillingham 1 Reputation point

2021-03-25T15:46:48.537+00:00

Hello @KranthiPakala-MSFT ,

Sorry for the delay I had seen your message.

The table outlined above is a very "heavy" transactional table at source.

We found the error across multiple tables, we think its because the ADF is assigning the numeric fields the equivalent of a "small int" data type and then when the numeric value exceeds that length we then get "out of range".

We also think this because we've narrowed it down further to only numeric columns where we have this problem and if we explicitly cast / convert them to a larger numerical format, we no longer get the problem.

If I'm right in thinking, ADF will analyze the top X rows and give a suggested data type?

As for the ODBC suggestion, this is how we connect to this source for our on prem extracts and is something we're considering here also moving forwards.

Cheers
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-04-05T23:43:30.437+00:00

Hi @Alex Fillingham ,

Sorry for the delay. I am waiting for a response form product team about your query. In the meantime could you please confirm if you have got a chance to try with ODBC connector and see the behavior? Do let us know if you notice any change in the behavior.

Thank you
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-04-07T17:06:08.707+00:00

Hi there,

Just checking to see if you have got a chance to try with ODBC connector and any change in behavior?

Thanks
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2021-04-09T16:17:26.387+00:00

Hi there,

We still have not heard back from you. Just wanted to check if you have got a chance to try with ODBC connector and any change in behavior?

Thanks

ADF - Index out of range

Activity