SqlCommand Times Out, CommandTimeout and ConnectionTimeout both = 0

Question

I'm running a large number of extracts from a SQL database via a C# Script Task within a SSIS package. The connection to the source is acquired from a connection manager within the package:

object rawConnection = Dts.Connections[sqlSpecItems["ConnectionManager"]].AcquireConnection(Dts.Transaction);
                SqlConnection connectionFromCM = (SqlConnection)rawConnection;

(splSpecItems is a Dictionary object that supplies the name of the connection manager to use)

The ConnectionTimeout property of the connection manager is set to 0. The connection string generated for the CM is:

Data Source=MyDatabase;User ID=MyUserName;Initial Catalog=MyDatabaseName;Persist Security Info=True;Asynchronous Processing=True;Connect Timeout=0;Application Name=MyPackageApplicationName;

The connection is used to return a SqlDataReader object as follows:

private SqlDataReader GetDataReaderFromQuery(string sqlQueryToExecute)
        {
            // connect to server
            SqlConnection sqlReaderSource = GetSourceSQLConnection();

            // create command
            SqlCommand sqlReaderCmd = new SqlCommand(sqlQueryToExecute, sqlReaderSource)
            {
                CommandType = CommandType.Text, 
                CommandTimeout = 0
            };

            // execute query to return data to reader
            SqlDataReader sqlReader = sqlReaderCmd.ExecuteReader();

            return sqlReader;               
        }

The operation fails with the error message:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Logged start and end times for the operation are typically around 50s apart, but can be up to 120s.

The source database is a Business Central cloud hosted SQL db. The failures occur on a small number of specific extracts, the larger ones (although by no means large in absolute terms, c20k rows). When attempting to query these via SSMS, there is typically a delay as the data has to be fetched from the source into memory, which I suspect to be the reason for the timeout (notwithstanding the setting of timeout = 0 for the connection and command). Note that once the failure has happened once, the data is in memory and so it doesn't repeat if I restart the job.

I've looked at various answers on this site, as well as across other sites. Nothing I have seen so far has given me any idea of what I might try to resolve this problem. Any help would be appreciated.

The default command timeout is 30 seconds and you need to make larger. Some software libraries use zero to ignore the timeout, but in Net library zero doesn't work. — jdweng
– jdweng, Commented Feb 17, 2021 at 11:14
Are you sure the failing code is using the synchronous ExecuteReader method as in the posted code and not BeginExecuteReader? Note: The CommandTimeout property will be ignored by older APM (Asynchronous Programming Model) asynchronous method calls such as BeginExecuteReader. SqlCommand.CommandTimeout Property — AlwaysLearning
– AlwaysLearning, Commented Feb 17, 2021 at 11:38
@jdweng I have set the timeout property on both the connection and the command to 600. I still get the failure, with logged start/end time difference of 82 seconds. — Iain Robertson
– Iain Robertson, Commented Feb 18, 2021 at 12:53
@AlwaysLearning The code posted is the exact code that is in the script task. The package is being called from T-SQL using the catalog procedures. I'm explicitly running it as synchronous: — Iain Robertson
– Iain Robertson, Commented Feb 18, 2021 at 12:56
exec [SSISDB].[catalog].[set_execution_parameter_value] @execution_id = @executionId , @object_type = 50 -- system parameter , @parameter_name = N'SYNCHRONIZED' , @parameter_value = 1; — Iain Robertson
– Iain Robertson, Commented Feb 18, 2021 at 12:58

Iain Robertson · Accepted Answer · 2021-03-29 08:05:28Z

2

So, I found the problem. Just in case it helps anyone else, the timeout wasn't throwing because of the reader. Rather, it was the SqlBulkCopy operation downstream of it that I pass the reader into. Adding:

bulkCopy.BulkCopyTimeout = 0;

Has cleared the problem...

answered Mar 29, 2021 at 8:05

Iain Robertson

211 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SqlCommand Times Out, CommandTimeout and ConnectionTimeout both = 0

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related