Time out exception in SSIS Dataflow task

Posted on 2008-10-28
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.
Question by:akulas
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
LVL 22

Expert Comment

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

LVL 17

Accepted Solution

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:
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.
LVL 17

Expert Comment

ID: 22825308
Great resource on connection pooling and connection leaks.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.


Author Comment

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?
LVL 17

Expert Comment

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.
LVL 22

Expert Comment

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.


Expert Comment

ID: 24222959

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.
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)

Expert Comment

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?

LVL 75

Expert Comment

by:Anthony Perkins
ID: 33705844

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query (lookup) 8 65
Need more granular date groupings 4 45
SQL Server Database Inventory 7 30
SQL Server replace between tags 6 30
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

752 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