Solved

SSIS Package Failed to Acquire Connection Intermittently

Posted on 2013-12-27
5
1,456 Views
Last Modified: 2016-02-10
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
Comment
Question by:ZekeLA
  • 3
  • 2
5 Comments
 
LVL 9

Accepted Solution

by:
QuinnDex earned 500 total points
ID: 39742200
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
 
LVL 1

Author Comment

by:ZekeLA
ID: 39742329
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
 
LVL 1

Assisted Solution

by:ZekeLA
ZekeLA earned 0 total points
ID: 39742409
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
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39742619
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
 
LVL 1

Author Closing Comment

by:ZekeLA
ID: 39749631
Suggested solution was perfect for Transact SQL job. I adjusted solution for SSIS Package job.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Over the last 2 years, I have been working on SSIS 2008. Really the tough tasks in SSIS are to deploy packages and pass parameters (Values from outside package). The latter is certainly a headache for developers, particularly for me. We had to ma…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now