Azure Data Factory - Errors "The statement did not return a result set" or "Unable to retrieve column metadata" inconsistently in Data flows activity

Question

Azure Data Factory - Errors "The statement did not return a result set" or "Unable to retrieve column metadata" inconsistently in Data flows activity

Mikael BURBAN 21

Hi,

I've got a Azure Data Factory pipeline triggered every da to populate a SQL Server database for reporting purposes. The alimentation is done through dataflows activity.
Inconsistently I encountered errors in the data flows with the following messages :

Or

When I re-run the activity without changing anything, it's working.
I've seen similar behaviors on some forums but I've never found an answer or ways to mitigate this behavior. Could you give some info on these errors ?

Thanks

KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-09-02T19:09:26.897+00:00
Hello @Mikael BURBAN ,

Thanks for the question and using MS Q&A platform.

As per my understanding you are receiving above error inconsistently when your dataflow is being executed. But if you rerun the pipeline then it is running fine without issues. Please correct me if I'm not accurate.

Based on your description below is my understanding:

Since your dataflow is writing to sink SQL table and failing inconsistently, and it runs fine if you rerun the pipeline, my prediction is that there is some other process that is blocking your insert operation. Could you please double check if the sink table is free from other process or is it being locked by some other process during the time of your dataflow execution.

As it is working fine if you rerun the pipeline, a few other workarounds you could explore are a) Try to set the Retry and Retry interval for your activity. b) Assuming your sink table is being used/locked by other processes, then try to schedule your pipeline in a different time so that you can avoid such inconsistent failures due to resource lock.

In case if the above steps don't help, please file a support ticket for deeper investigation of the issue.

Hope this info helps. Do let us know how it goes.

Thank you
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-09-05T23:52:03.917+00:00

Hello @Mikael BURBAN ,

Just checking to see if my previous response was helpful to resolve your issue. In case if you have further questions, do let us know.

Thank you
Mikael BURBAN 21 Reputation points

2022-09-06T06:54:16.687+00:00

Hello @KranthiPakala-MSFT ,

Thanks for your answer and your ideas.

1 - Database : Data factory is the only process running at that time (the other processes are powerbi datasets that have scheduled refresh times which are not at this time), so except if Data factory is generating the lock, I don't think that it could come from that. We don't have much logs activated on the database to monitor the activity but I'll see what I can find.

2- Regarding the retry, I will see if this can be implemented. The thing is that it's a long process (each dataflow goes on for 20 minutes) so the retry will make the whole job longer, and then we could miss the time where users need their data. So it can mitigate the problem but I'll rather like some hints to correct the problem.

3 - OK thanks, I'll consider opening a ticket.

Regards
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-09-08T08:22:15.507+00:00

Hello @Mikael BURBAN ,

Thanks for the response.

Yes, I would suggest tracking down from the SQL logging and narrow down root cause.
And yes, retry is a kind of mitigation but I agree that a permanent fix would the ideal approach.

In case if you have trouble in identifying the root cause even after going through the above series of event, I suggest filing a support ticket and do share the SR# with us here for tracking it down.

Thank you. Do let me know if you have any questions.
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-09-15T16:47:01.477+00:00

Hello @Mikael BURBAN ,

Just checking to see if you have any update regarding filing support ticket. If you have already filed it, could you please share the SR# with us here. Thanks

Your answer

KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-09-02T19:09:26.897+00:00

Hello @Mikael BURBAN ,

Thanks for the question and using MS Q&A platform.

As per my understanding you are receiving above error inconsistently when your dataflow is being executed. But if you rerun the pipeline then it is running fine without issues. Please correct me if I'm not accurate.

Based on your description below is my understanding:

Since your dataflow is writing to sink SQL table and failing inconsistently, and it runs fine if you rerun the pipeline, my prediction is that there is some other process that is blocking your insert operation. Could you please double check if the sink table is free from other process or is it being locked by some other process during the time of your dataflow execution.

As it is working fine if you rerun the pipeline, a few other workarounds you could explore are a) Try to set the Retry and Retry interval for your activity. b) Assuming your sink table is being used/locked by other processes, then try to schedule your pipeline in a different time so that you can avoid such inconsistent failures due to resource lock.

In case if the above steps don't help, please file a support ticket for deeper investigation of the issue.

Hope this info helps. Do let us know how it goes.

Thank you
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-09-05T23:52:03.917+00:00

Hello @Mikael BURBAN ,

Just checking to see if my previous response was helpful to resolve your issue. In case if you have further questions, do let us know.

Thank you
Mikael BURBAN 21 Reputation points

2022-09-06T06:54:16.687+00:00

Hello @KranthiPakala-MSFT ,

Thanks for your answer and your ideas.

1 - Database : Data factory is the only process running at that time (the other processes are powerbi datasets that have scheduled refresh times which are not at this time), so except if Data factory is generating the lock, I don't think that it could come from that. We don't have much logs activated on the database to monitor the activity but I'll see what I can find.

2- Regarding the retry, I will see if this can be implemented. The thing is that it's a long process (each dataflow goes on for 20 minutes) so the retry will make the whole job longer, and then we could miss the time where users need their data. So it can mitigate the problem but I'll rather like some hints to correct the problem.

3 - OK thanks, I'll consider opening a ticket.

Regards
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-09-08T08:22:15.507+00:00

Hello @Mikael BURBAN ,

Thanks for the response.

Yes, I would suggest tracking down from the SQL logging and narrow down root cause.
And yes, retry is a kind of mitigation but I agree that a permanent fix would the ideal approach.

In case if you have trouble in identifying the root cause even after going through the above series of event, I suggest filing a support ticket and do share the SR# with us here for tracking it down.

Thank you. Do let me know if you have any questions.
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-09-15T16:47:01.477+00:00

Hello @Mikael BURBAN ,

Just checking to see if you have any update regarding filing support ticket. If you have already filed it, could you please share the SR# with us here. Thanks

Share via

Azure Data Factory - Errors "The statement did not return a result set" or "Unable to retrieve column metadata" inconsistently in Data flows activity

Your answer