0

I am getting this error randomly:

Timeout expired. "Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed before completion of the operation or the server is not responding."

Sometimes the package just runs fine without any errors. I am confident there is no syntax or database error in the SQL script I wrote. I also changed the connection timeout to 30 but I still get this error. Any suggestion would be highly appreciated.

I have series of data flow task, and I have one script task as well. I get this error in the script task when I try to run a select query, Select query run fine if I run it on SQL server management studio. I take data from one server and insert it into a database on the different server. I hope that makes sense.

Also, when i debug the script it hangs right on that query but when I run this in SSMS it gives result in like 30 seconds. Could it be possible that there is some network issue connecting to SQL server through is?

billinkc
  • 59,250
  • 9
  • 102
  • 159
SQLSERVERDAWG
  • 57
  • 2
  • 10
  • Can you give more information about what you are trying to do and when you get this error. – Abhay Chauhan Sep 29 '16 at 16:41
  • It is exactly as the error says, one of your queries is timing out. You should first address the query that is taking a long time and see if it can be optimised, then if it is already optimised and is just retrieving a lot of data (not uncommon for SSIS) then change the timeout to something higher than 30, or even 0 (no timeout) if you are confident that this won't cause issues elsewhere. – GarethD Sep 29 '16 at 16:48
  • I already checked it. If I run the query in SSMS it takes literally 20 seconds..so I don;t think it has anything to do with the query optimization – SQLSERVERDAWG Sep 29 '16 at 17:06
  • It really depends on the query, 20 seconds is all relative. If you are extracting 10 million rows, then it is pretty fast, if you are extract 10 thousand rows then it is pretty slow. A variation of +-50% is quite large, but it is not out of the question, all you need is a bit of network latency, or some table locking, and something that already takes 20 seconds isn't going to take that much extra to push it over 30 seconds - hence sporadic timeouts. Just up the timeout to 1 minute. – GarethD Sep 29 '16 at 17:16

0 Answers0