SSIS 2012 with VS 2013 connection fails

I have a new SSIS project that I upgraded from VS 2010. The task starts by opening three databases. The connections to all three databases refer to the same server and same SQL instance. The only difference is the database. When I run the project I get this error message.
"[Account [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "ERPCurrentDB" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed."

I can browse the data within the task but I cannot run the project. I have tried switching to 32 bit and setting DelayedStart to True and neither has helped. What else can I check?
LVL 1
rwheeler23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Rainer JeschorCommented:
Hi,
are there any more messages (details about the error) in the log?
What Kind of connection do you use? OLE DB, SQL Native?
Thanks and HTH
Rainer
rwheeler23Author Commented:
The only other messages were about connection timeout. The connection type is OLE DB. The only solution was to start a new project, create new connections and import the tasks.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
check this solution

The AcquireConnection method call to the connection manager failed with error code 0xC0202009
http://blogs.msdn.com/b/ramoji/archive/2009/03/10/the-acquireconnection-method-call-to-the-connection-manager-failed-with-error-code-0xc0202009.aspx

Analysis: Since the account is sysadmin, the error message is misleading. Also, the second message was of no use as it didn't detail why it couln't acquire connection inspite of having the right permissions. Interestingly, the crux lies in the last part of the message where it says validation failed.

Fix: After breaking our heads why the validation has failed, we couldn't find any reason but noticed that the DFT-MyTask has "DelayValidation" property set to false in the package. So, we changed the DelayValidation property of DFT-MyTask to true and re-ran the job and it succeeded.  

Root Cause: As per our understanding, if DelayValidation is not set to true, SSIS engine uses the design time values of a task until it actually runs a task. Since we are dynamically setting the connection manager, instead of using the dynamically set value (Which it uses anayway while running the task), it tried to validate the task with the design time values set for the connection manager. Unfortunately, the service account with which the job is running doesn't have access to the server and database set during the design time for the connection manager. So, the validation of the task failed causing the job to fail.

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
rwheeler23Author Commented:
Thanks. That was the one option I had not tried.
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.