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.