Failed to acquire connection (loop container)

Question

Failed to acquire connection (loop container)

Tara-1044200

SSCoach

Points: 15948
More actions
July 11, 2013 at 9:46 am

#279893

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

kl25 Hall of Fame Points: 3752 More actions · Answer 1

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. :w00t: It's probably a permission issue.

Tara-1044200 SSCoach Points: 15948 More actions · Answer 2

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?

kl25 Hall of Fame Points: 3752 More actions · Answer 3

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?

Phil Parkin SSC Guru Points: 246686 More actions · Answer 4

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.