• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1841
  • Last Modified:

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
0
ZekeLA
Asked:
ZekeLA
  • 3
  • 2
2 Solutions
 
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
0
 
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.
0
 
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.
0
 
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
0
 
ZekeLAAuthor Commented:
Suggested solution was perfect for Transact SQL job. I adjusted solution for SSIS Package job.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now