Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Time out exception in SSIS Dataflow task

Posted on 2008-10-28
10
Medium Priority
?
5,107 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 1500 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

783 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