Solved

SSIS Package Failed to Acquire Connection Intermittently

Posted on 2013-12-27
5
1,595 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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