Поделиться через


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 removed

  • Anonymous
    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, Ramoji

  • Anonymous
    June 19, 2009
    The comment has been removed

  • Anonymous
    July 30, 2009
    The comment has been removed

  • Anonymous
    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 Jamie

  • Anonymous
    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 Shankar

  • Anonymous
    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 shankar

  • Anonymous
    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 removed

  • Anonymous
    December 06, 2011
    Thanks for the help

  • Anonymous
    May 08, 2012
    The comment has been removed

  • Anonymous
    May 09, 2012
    The comment has been removed

  • Anonymous
    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 TRUE

  • Anonymous
    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 removed

  • Anonymous
    June 17, 2013
    Saswata Nayak: thanks a lot!. You're on fire!!.

  • Anonymous
    July 12, 2013
    The comment has been removed

  • Anonymous
    August 12, 2013
    The information you passed with DelayValidation is incorrect.

  • Anonymous
    October 02, 2013
    The First RCA is very good it worked for me

  • Anonymous
    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 post

  • Anonymous
    May 19, 2015
    The solution above worked for me as well

  • Anonymous
    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.