SSIS Data Flow Task Hangs

  • I have a data flow task that should be moving around 16k records from a SQL 2012 instance over to a  SQL 2005 instance.  However, the task never completes.

    The source is a query... and if I limit the query to top 25 the task completes..   However, if I allow the source to return more than 25 or so rows the task never errors, and never completes.

    I have tried several drivers, and several parameters (batch size, insert commit) but the result is the same.  It never errors, and never completes...  and no rows are moved to the destination.

    If I change the destination of the package to another instance, it runs fine.. so it is something either on the destination server, or on the network segment between... However, troubleshooting efforts up until now have produced no issue.

  • As a guess, how much memory do you have free when you run that?  SSIS runs in its own memory space, so it is possible (and is my current best guess) that it is paging to disk which is an incredibly slow operation.

    Failing that, you could have blocking on the source server so when you try to pull the 16,000 rows, SQL is telling you to wait.  Might not hurt to check the wait stats or while the package is running, EXEC sp_who2 and look for blocking.

    Since it runs fine if you change to another instance, my guess is more on the blocking side than the memory side,but I would check both options out.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply