SSIS Package Failed to Acquire Connection Intermittently

I have an SSIS package that first loads data from a source DB on another server to an intermediate DB on the same server as the SSIS package and then secondly loads data from the intermediate DB to a final DB (also on the same server as the SSIS package). I have 2 versions of this package, one where the initial DB is SQL 2005 and one where it is SQL 2008 R2. The destination server in all cases is SQL 2012.

Sometimes when I run the package overnight, I get an error that the SSIS package failed to acquire a connection, usually due to a login timeout error. My master package contains Execute SQL Tasks and Execute Package Tasks. The Package Tasks are all Data Flow Tasks. I'm using 3 project level connection managers, one for each database.

I have not seen the error when I run the packages during the day and it doesn't happen every night, just sometimes. In some cases, the master package will run several data flow tasks fine and then fail to acquire the connection on another data flow task, even though the connections are exactly the same. In the most recent failure, the connection to the intermediate database (on the local server) worked fine but when it executed the first task for the final database (still on the local server), it failed to acquire a connection.

1. Is there a way to handle such errors and have it retry the connections so it doesn't fail as often?
2. Is there a likely reason why the package is not able to acquire a connection?
3. Is there a way for me to determine why the connection isn't being acquired?

I've attached a screenshot of the error.

Thank you in advance.
Failed-to-Acquire-Connection.JPG
LVL 1
ZekeLAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QuinnDexCommented:
i had this when i had to update a product catalogue every night, i set up a job that checked every hour for a flag in the database if it didnt find it it ran the SP setting the flag on completion

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZekeLAAuthor Commented:
Thanks QuinnDex. If I can't find a more concrete reason for the problem, I'll probably alter the package to set a flag and just run the package every hour or so within a specified period. But the package takes about 30 - 60 minutes and is designed to run during non-peak times so I can't just run it until it's successful. But since it's intermittent, I would hope it would be sufficient.
ZekeLAAuthor Commented:
I think I found an even better way than a table flag. I'm running the packages overnight from a SQL job. SQL Job steps have a retry feature which lets you specify how many times a step should run and the interval between retries. This would probably be best for my situation.
QuinnDexCommented:
that will work.

the reason i was setting a flag is i had other sp's set to run but they couldnt if the update hadnt run
ZekeLAAuthor Commented:
Suggested solution was perfect for Transact SQL job. I adjusted solution for SSIS Package job.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.