SQL Connection Timeout

Question

I'm in Data Tools and have a script to select data and write to a csv file which works fine, see connection sample below.

I need to run this on a large remote table and it select takes about 4mins in total to select but after 30 seconds I'm getting a timeout.

I changed the Connection Manager Timeout to 300, 600 and 0 but it didn't change the time when it errors.

So I guess this timeout is only the time to connect to server and not runtime?

How would I make it wait longer for running the job?

Thanks.

sample script

 //USE ADO.NET Connection from SSIS Package to get data from table
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["DB_Conn_TEST"].AcquireConnection(Dts.Transaction) as SqlConnection);

//Execute Stored Procedure and save results in data table
 string query = "EXEC " + StoredProcedureName;
SqlCommand cmd = new SqlCommand(query, myADONETConnection);
DataTable d_table = new DataTable();
d_table.Load(cmd.ExecuteReader());
myADONETConnection.Close();

Answer 1

This is in the error:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Answer 2

Also to add If I look at the string in connection manager: DB_Conn_TEST

Data Source=JA-DB;Initial Catalog=Test_Jobs;Integrated Security=True;Connect Timeout=300;Application Name=SSIS-Package-{35489926-EB50-4E8B-B87E-753832BB8877}JA-DB.Test_Jobs;

It has Connect Timeout=300, is the script maybe not picking this up?

Answer 3

Connect timeout is the timeout waiting for the server to respond to the initial connection request, not for executing commands. To set the timeout for executing a command you need to set the CommandTimeout property of the command object.

var cmd = new SqlCommand(...);
cmd.CommandTimeout = 4 * 60; //240 seconds

Michael Taylor
http://www.michaeltaylorp3.net

Answer 4

Thanks Michael,

OK that makes sense I was trying to change the wrong setting, I just tried adding the line but getting an error on:

SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection.CommandTimeout = 4 * 60; //240 seconds
myADONETConnection = (SqlConnection)(Dts.Connections["DB_Conn_TEST"].AcquireConnection(Dts.Transaction) as SqlConnection);

I'm just going to have a search around to try and figure out the problem but can you see where I have gone wrong?

Answer 5

Arghh sorry Michael I've been a numpty I've just spotted where this was meant to go, after this line:

cmd = new SqlCommand(query, myADONETConnection);
cmd.CommandTimeout = 4 * 60; //240 seconds

Thanks for your help.