SSIS Data Flow Task Hangs

  • kemp.jerry

    SSC Rookie

    Points: 48

    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.

  • Mr. Brian Gale

    SSC-Insane

    Points: 23059

    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.

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

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