Timeout issue with Data Flow Task

• rob cushen

  Old Hand

  Points: 319

  More actions

  August 27, 2007 at 5:27 am

  #178241

  It appears that I have a Data Flow Task that is timing out after about 1

  minute....

  I am transferring data from a SQL 2000 box to a 2005 box.... if I click

  Preview data (within the Data Flow pane) I get a timeout error.... if I run

  the task it will run for hours without transferring any data.

  If  I open the SQL 2000 source view it renders in about 7 seconds, so I do

  not understand why the problem is occurring... only 30,000 rows of data with

  3 columns...

  How can I increase the timeout ?

  Any reasons why this should take so long ?

  I did solve this problem by turning the task into an Execute SQL Task... I created an SQL statement to replace it, but I still want to know how to increase the timeout.

  Thanks !

   

   

• Dan Colbert-387424

  SSCrazy

  Points: 2195

  More actions

  August 27, 2007 at 2:01 pm

  #729633

  Here are a couple of things you can look at before changing the timeout (which I honestly can't help you with - have you tried BOL?):

  1. How much memory do you have on the SQL box running the package?  Everything is pulled into memory, so if you have 30000 rows at - let's say - 150KB per row (I'm guessing, here), you've now attempted to pull 4.5 gig of data into memory.  That would definitely slow things down...
  2. Make sure your source data access mode is "SQL Command" instead of "Table or View" - it's (most of the time) more efficient.
  3. In the properties of the data flow task, try playing around with the "DefaultBufferMaxRows" - it defaults to 10000.
  4. Check your indexes on your source table.  Seven seconds sounds like a long time for a 30000 row return set.

  Hope these suggestions help!

  Dan