Azure datafactory pipeline - failed

Question

Hi,

We have an adf pipeline which runs parallelly and copy data from landing folder ( Data Lake) to staging table. We have 30 staging tables and at a time 30 tables are get copied. Usually it takes one hour. But now is taking more time and also getting failed at lookup activity. Attached screenshot for error details. Kindly assist

Answer 1

Hi @Anonymous ,
Thankyou for using Microsoft Q&A platform and posting your query.
With the information you have provided, it seems that the query used in the lookup which is actually running in SQL server is getting suspended or blocked. Could you please check running the query directly in SQL server if it's behaving the same way. You can use the following sql query to check the status :

SELECT sqltext.TEXT,  
req.session_id,  
req.status,  
req.command,  
req.percent_complete,  
req.reads, req.writes,  
req.cpu_time,  
req.total_elapsed_time  
,* FROM sys.dm_exec_requests req  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext  

Also , please try to decrease the timeout of the lookup to around 30 mins and set retry =1 . It may help in auto-cancelling the query if it's stuck and trigger it again automatically as retry>0.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators