Solved

Time out exception in SSIS Dataflow task

Posted on 2008-10-28
10
5,052 Views
Last Modified: 2013-11-10
Description: System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached

This is the detailed exception.  Why do we generally get this exception and how can we avoid it.
0
Comment
Question by:akulas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22824794
Right-click in your connection on connection manager, and select tab ALL

Try to change the values of connection, mainly timeout.. as you can see in the image

Helped?
regards!
EE-TimeOut.JPG
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
ID: 22825254
Make sure you and other applications are closing connections. You know that piece of code at the end of a sql connection where we write something like:
connection.close()
It sounds like you have a leak somewhere. With every open should also be a close. Look for open connections at the database level. See if you can tell what is leaving the connections open.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22825308
Great resource on connection pooling and connection leaks.
http://www.15seconds.com/issue/040830.htm
 
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:akulas
ID: 22826128
Thank you Experts for your suggestions.  But I cannot reproduce this error every time.  It happens very randomly.  I will check for the connections I opened and I will close them manually.  I am pretty sure that I did them.  

I will make the timeout to 0 as you have mentioned.   Can you tell me what this actually means?
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22826167
Randomly suggests you have a leak. Connections are being opened and not closed. Next time you get the error, run the sp_who stored procedure to get some session details.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22829718
Dear akulas,
Exist in this connection three timeouts: (General, Connect and Command)

General should be the overall timeout for the connection.  
Connect should be just for the connection.  If the connection hasn't been established by the timeout (or the default), it will stop trying to connect.  
Command timeout is likely the timeout SSIS will wait for a command to complete.

Helped?
Regards
0
 

Expert Comment

by:RyanBar
ID: 24222959
Hi.

I have found that when you use the connection type: ADO, the timeout is automatically set to 30 seconds (Regardless of which timeout option you manually change). I had al 3 timeout options set to 0, which should indicate that no timeout is required.
I fixed my timeout problem by changing the connection type to "OLE DB".
I hope this helps someone.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24224754
>>ADO, the timeout is automatically set to 30 seconds <<
That really depends on whether you are talking about ADO or ADO.NET.  In ADO both the Connection and Command default Timeout was 30 seconds.  In ADO.NET the Connection default Timeout is set to a more reasonable 15 seconds. But in both ADO and ADO.NET you can programatically set it to any value you chosse including 0 (infinite)
0
 

Expert Comment

by:mwidholm
ID: 33703714
We're having this issue on the Execute SQL task in SSIS.  I was under the impression that it would close the connection itself- one of the benefits of the connection manager in SSIS?

Once it reaches the 100 limit, it gives us the timeout error.  Does anyone have insight to:
1. Why this isn't being managed through SSIS?
2. How we can close the connections?

Thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33705844
mwidholm,

You may have better luck if you post your own question, rather than piggy-backing on this closed thread.
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question