The AcquireConnection method call to the connection manager failed with error code 0xC0202009
Sometimes the actual error lies somewhere and the error message directs us to look at something else. We end up wasting time in this process as the error message diverted us. Recently, we came across one such kind of scenario while calling a SSIS package in one of the Job steps on SQL Server 2008. The details are as follows.
Error: Login failed for user 'Domain\alias'. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Cannot open database "MyDatabase" requested by the login. The login failed.". End Error Error: 2009-02-26 03:08:23.93 Code: 0xC020801C Source: DFT-MyTask OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MyServer.MyDatabase" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2009-02-26 03:08:23.93 Code: 0xC0047017 Source: DFT-MyTask SSIS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Progress: 2009-02-26 03:08:23.93 Source: DFT-MyTask Validating: 50% complete End Progress Error: 2009-02-26 03:08:23.93 Code: 0xC004700C Source: DFT-MyTask SSIS.Pipeline Description: One or more component failed validation. End Error Error
Job Logic: We are using a script task to dynamically set the connection manager. We are pulling data from a source server using a select query and putting it into a table on a destination server. We are surprised to see login failed error in the first part of the message. Because, the account "Domain\alias" is a sysadmin on the source as well as destination server and the job is also running under the same account.
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.
Comments
Anonymous
April 03, 2009
The comment has been removedAnonymous
April 04, 2009
Hi Bulent, It looks like a permission issue since the package is running fine. When you are running it manually, it is running under your domain account, which has got the required access to the windows NT file system to create the target text file. But the account with which the job is running might not have the required permissions to create the target text file. So, please grant write access on the windows folder (Where you are going to create the text file) to the service account under which the SQL Agent job is running. Regards, RamojiAnonymous
June 19, 2009
The comment has been removedAnonymous
July 30, 2009
The comment has been removedAnonymous
December 05, 2009
I had the same problem even setting the Delayvalidation to true didn't work. The package had previously used a configuration file and I deleted them from the collection in the pacakge and set connections manually. Still didn't work. I added the configuration files back to the package, then it worked! It's as if once you use configuration files, you've etched them in stone in your package!Anonymous
April 25, 2010
Whether the best practice of setting DelayValidation = true can be extended to all tasks created in a package.Any NI of it.Anonymous
June 28, 2010
Hi Jamie, Thanks for the feedback. I didn't mean to set the property to true as a blind standard. It is my bad and I should have been more specific. I wanted to say that the property should be set to true in case of dynamic connections, which was the situation we faced. I updated the blog now :-)Anonymous
June 28, 2010
Hi Ramoji, Much appreciated, thank you. I hope you didn't mind my rather pertinent reply, I was just a bit shocked when I read that! Apart from that, good post methinks. cheers JamieAnonymous
July 26, 2010
Hi Jamie, I have a question on delayvalidation. The connection managers in my package are set to use package configurations so that connection string is configurable. Is it advisable to set DelayValidation = True at Connection Manager level OR at the entire DTF level which consumes the connection. Thanks ShankarAnonymous
July 26, 2010
Hi Jamie, Another observation I had is, If DelayValidation is not set to true, I get this "The AcquireConnection method call to the connection manager failed with error code 0xC0202009" error only some times. Its like, if I re-run the package , it can work next time. My question is, if SSIS performs validation using design time settings, why isnt this consistent with the error everytime. Please share your views thanks shankarAnonymous
August 04, 2010
hey thanx...it worked for me...:)Anonymous
April 24, 2011
I am also facing the same error even after setting DelayValidation to true!! please help.Anonymous
May 31, 2011
some time you are using the following connection string in which provider is ACE OLEDB rather than you can use "Jet.OLEDB.4.0". ACE OLEDB provider Connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::sourcefilepath] + ";Extended Properties="Excel 12.0;HDR=YES"; Jet OLEDB provider connection string "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::sourcefilepath] + ";Extended Properties="Excel 8.0;HDR=YES";"Anonymous
July 28, 2011
The comment has been removedAnonymous
December 06, 2011
Thanks for the helpAnonymous
May 08, 2012
The comment has been removedAnonymous
May 09, 2012
The comment has been removedAnonymous
July 09, 2012
I had the same eerror and i changed the DelayValidation Property to TRUE and am still getting the same error.Anonymous
July 12, 2012
change the transactionOption to: NotSupported !! And delayValidation to TRUEAnonymous
September 11, 2012
Hi we are also facing the same issue inspite of delay validation set to true.. what can be the other possibilites of the error...? Thanks in advance..Anonymous
April 09, 2013
The comment has been removedAnonymous
June 17, 2013
Saswata Nayak: thanks a lot!. You're on fire!!.Anonymous
July 12, 2013
The comment has been removedAnonymous
August 12, 2013
The information you passed with DelayValidation is incorrect.Anonymous
October 02, 2013
The First RCA is very good it worked for meAnonymous
May 25, 2014
Thanks. The above information really helped to me and my issue got resolved.Anonymous
August 25, 2014
I deleted the data source and recreated it with the same name. All of the SSIS controls that used the old data source threw errors so I repointed them to the new data source. I could then run the package without issue. Tip - Running the package should give you a better error than 0xC0202009.Anonymous
October 27, 2014
Thank you very much..Very helpful postAnonymous
May 19, 2015
The solution above worked for me as wellAnonymous
July 14, 2015
Having the destination file highlighted in file explorer caused this error. "Unselecting" the file solved the problem.Anonymous
February 02, 2016
I had exactly the same issue as rjq 12 Jul 2013 9:27 AM mentioned above. Changing this property back to EncryptSensitiveWithUserKey solved my problem.