SSIS Package Performance and Timeout Exception

Recently I deployed a package to production and started getting this error;

System.Data.SqlClient.SqlException (0x80131904): Execution 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     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PostExecute()     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPostExecute(IDTSManagedComponentWrapper100 wrapper)  ClientConnectionId:b35cae06-1c27-4b61-9a32-6bf173753803  Error Number:-2,State:0,Class:11

After doing some research I found this;

SQL Server remote query timeout default value is 600seconds which is 10 minutes wait. The server that runs the package will wait for this amount of time before terminating remote sessions.

Lets increase this. I am setting this to 7200 seconds that’s 120 min that’s 2 hours. both on client and server.

Run this command on server;

EXEC sp_configure 'remote query timeout', 7200
RECONFIGURE

This might take some time. On my server it took 4 minutes.

Now run SSIS Package without re-deployment. Will it work? If no, then you need to set timeout in destination task;

For script component We can use something like this;

SqlCommand cmd = new SqlCommand(vSqlStatement, sqlConn);
/*
   7200 sec = 120 min = 2 hours. This can be set to 0 for non timeout at all
   Will this work? It also depends on server timeout settings. In most SQL install, SQL default timeout for remote queries is 600 seconds, 10 minutes.
*/
cmd.CommandTimeout = 7200;
sqlReader = cmd.ExecuteReader();

SSIS moves data using buffers. The default buffer size is 10 megabytes, with a maximum buffer size of 2^31-1 bytes. The default maximum number of rows is 10,000.

IF you look into destination buffer size, it is set to 0, which is default. If server does not support more horsepower then we can change this value to reduce buffer size, say change it from 0 to 500.

There is another good property that can help to adjust buffer size is AutoAdjustBufferSize. Set AutoAdjustBufferSize property to true on DataFlow Task. This helps to improve performance.

Resources

Execution timeout expired the timeout period elapsed prior to completion

https://stackoverflow.com/questions/16651945/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver15

https://www.mssqltips.com/sqlservertip/4221/improving-data-flow-performance-with-ssis-autoadjustbuffersize-property/

https://www.mssqltips.com/sqlservertip/3217/improve-ssis-data-flow-buffer-performance/