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.

  • Phil Parkin

    SSC Guru

    Points: 244656

    Are you running this in Debug mode in Visual Studio?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • kemp.jerry

    SSC Rookie

    Points: 48

    I have run it via debug, as well as via a sql agent job.  Same results.. Never errors, never completes.

  • Phil Parkin

    SSC Guru

    Points: 244656

    "If I change the destination of the package to another instance..."

    Is that another 2005 instance?

    Is there anything going on in the data flow, or is this a straight source --> destination job?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • kemp.jerry

    SSC Rookie

    Points: 48

    It's just a straight source -> destination task.    If I add " top 25" to the query, it succeeds.  It only hangs when the row count increases.

  • Phil Parkin

    SSC Guru

    Points: 244656

    kemp.jerry wrote:

    It's just a straight source -> destination task.    If I add " top 25" to the query, it succeeds.  It only hangs when the row count increases.

    OK. You answered one of my questions, what about the other one?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • kemp.jerry

    SSC Rookie

    Points: 48

    The source is SQL 2012, and the destination that is having trouble is SQL 2005.

    As a test, I have re-pointed the package to another SQL 2012 server, and it succeeds in less than 20 seconds.

  • kemp.jerry

    SSC Rookie

    Points: 48

    Experimenting with different options... if I change the Data access mode in the OLE DB Destination to "Table or view" instead of "Table or view- fast load" it does load the data.. albeit in batches of 20 rows  (when this access mode is chose: the batch size is no longer configurable)...    Also FYI: I am using SQL Server Native Client 11.0 as the driver.

  • Phil Parkin

    SSC Guru

    Points: 244656

    It's been so long since I've had to deal with 2005 that I cannot remember any of its idiosyncrasies, so I don't think I can assist further with this one.

    You might also try using an ADO connection, as you are experimenting.

    At your next planning meeting, I'd suggest putting 'SQL Server Upgrades' on the agenda. Even 2012 is starting to look long in the tooth now.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jeffrey Williams

    SSC Guru

    Points: 88595

    It sounds like a problem on the destination - I would suspect something is blocking the insert into that 2005 instance once you exceed a certain number of rows.

    Are you inserting into a staging table - or to a final table?  If it is not to a staging table - have you tried using a blank table with no current rows as the destination?

    In debug mode - do you see all rows as being selected from the source, and the hang occurs only on the destination?  Or - does it select ~25 rows and then hang?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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