Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Time out exception in SSIS Dataflow task

Posted on 2008-10-28
10
Medium Priority
?
5,082 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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 ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
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.

670 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