Avatar of ZekeLA
ZekeLA
Flag for United States of America asked on

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
Microsoft SQL ServerSSIS

Avatar of undefined
Last Comment
ZekeLA

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
QuinnDex

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ZekeLA

ASKER
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.
SOLUTION
ZekeLA

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
QuinnDex

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
ZekeLA

ASKER
Suggested solution was perfect for Transact SQL job. I adjusted solution for SSIS Package job.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck