Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Failed to acquire connection (loop container) Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 9:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, January 9, 2014 7:44 AM
Points: 988, Visits: 2,945
I am getting this error half way through the loop container in SSIS, why would it fail connection after certain point of successfull execution?

Failed to acquire connection "server.DB". Connection may not be configured correctly or you may not have the right permissions on this connection.
Post #1472665
Posted Thursday, July 11, 2013 3:23 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:04 AM
Points: 488, Visits: 1,858
Tara-1044200 (7/11/2013)
I am getting this error half way through the loop container in SSIS, why would it fail connection after certain point of successfull execution?

Failed to acquire connection "server.DB". Connection may not be configured correctly or you may not have the right permissions on this connection.


Hard to say. If the connection string is dynamic, check the permissions on the server. I'd also be checking permissions to underlying db objects for that point in the loop. When I've received this error message, it's usually because I forgot to take care of some permission--even when I thought all of the permissions were granted. I've learned through experience to take this message at it's word. It's probably a permission issue.
Post #1472803
Posted Friday, July 12, 2013 7:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, January 9, 2014 7:44 AM
Points: 988, Visits: 2,945
If its a permissions issue how could that loop make half way through hitting the same tables and database/server?

just so you know my loop iterates almote 50k times, do you know of any limitation here?
Post #1473026
Posted Friday, July 12, 2013 7:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:04 AM
Points: 488, Visits: 1,858
Tara-1044200 (7/12/2013)
If its a permissions issue how could that loop make half way through hitting the same tables and database/server?

just so you know my loop iterates almote 50k times, do you know of any limitation here?


Didn't know much about what the loop was doing--you didn't say. My thought was that if it was hitting different servers with a dynamic connection string, it could be permissions on the server hit during that iteration. The other thought was that if it was hitting different tables, views or whatever, it might be permissions on the particular object for that iteration. If it's the same tables on the same server, it's probably not permissions. What I've found on the occasions where I've gotten this message is that each time it did turn out to be a permissions issue--sometimes when I was sure it wasn't. But it's hard to see how that's the case here if it's the same server and db objects each loop.

I don't know of any limitation on the number of iterations.

[Edit to add a question] Does it stop at the same iteration each time?
Post #1473046
Posted Monday, July 15, 2013 12:05 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
Tara-1044200 (7/12/2013)
If its a permissions issue how could that loop make half way through hitting the same tables and database/server?

just so you know my loop iterates almote 50k times, do you know of any limitation here?


50k iterations of a foreach loop is quite a lot - does the package run for a long time? Perhaps there is a more optimal solution.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1473828
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse