Setting timeout option for Script Task in SSIS

Question

I have a script task that executes a dynamically created SQL restore statement. This has worked without a problem for the first 6 runs, but then the .bak files grew too large to run in under 30 sec.

I have looked everywhere I can think of for a timeout option. I have reset the Connection Manager's General Timeout to 500 (from what I understand that is in seconds). None of this has worked yet.

Where / how do I set this value (or can I)?

BIDS Version Info: Microsoft Visual Studio 2008 Version 9.0.30729.4462 QFE

Microsoft .Net Framework Version 3.5 SP1

Answer 1

You'd need to post your code but it's most likely not the Connection Manager that is timing out but the query (they are different timeouts). Assuming you are using a SqlCommand object to run your query, set the CommandTimeout to something beyond the default 30 seconds.

Code approximately

// Set the query execution timeout to 500 seconds
mySqlCommand.CommandTimeout = 500;
mySqlCommand.ExecuteNonQuery();

Of course, you'll want to be code defensively but this addresses the crux of your need.