Solved

Time out exception in SSIS Dataflow task

Posted on 2008-10-28
10
4,976 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

785 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