When I'm doing ETL in SSIS, the data flow task "pauses" reading from the source transactional Live IO DB (the one on the left). It stops reading the data and "pauses" on random rows and then does nothing with no error output. I have tried waiting for 30 minutes for it to throw an error, but nothing happens (it just spins forever).

I have narrowed this issue down to the source live transactional DB on the top left side (the same process with a local copy of that live IO DB works fine). When I narrow down data selection from the live source DB to start from the row when it stopped last time, it'll then read again so many rows and then stop again. I have checked the data in the few rows where it stopped and it's fine.

Not sure what causes this issue or how to troubleshoot this further.

enter image description here

  • Check for blocking on the source server query when the package stalls.
    – Dan Guzman
    May 16 '19 at 11:22
  • @DanGuzman I'm not sure what sort of blocking am I looking for or how to check for it. Could you please explain this further?
    – Gazel
    May 16 '19 at 11:36
  • 2
    One way is from SSMS. Right-click on the server in Object Explorer and select Reports-->Performance Dashboard. This will show a waiting requests chart. Click on locks to drill-down and view the blocking chain. If you have a DBA, they may be able to help.
    – Dan Guzman
    May 16 '19 at 11:43
  • @DanGuzman Thanks. Your comment has helped to solve this.
    – Gazel
    May 16 '19 at 20:23

DanGuzman from the above comments has pointed me in the right direction. The problem was with queries getting suspended in SQL server. To resolve this issue, I used SELECT statement with WITH (NOLOCK) option.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.