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
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.