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 (31)

  1. Bulent says:

    Hi Romaji,

    I have a DFT that needs to export data to text file which the file name is variable.  I have set the expression, protection level to don’t save sensitive and delayvalidation to true.  When I run the package manually it works just fine when when I scheduled as a job it fails.  Here is the error message I get back in the job history

    Message

    Executed as user: DOMAINDOMAINACCOUNT. …035.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:07:37 AM  Error: 2009-04-04 00:07:54.47     Code: 0xC004706C     Source: Prepare and Write to Text File DTS.Pipeline     Description: Component "component "Export data to Text File" (1724)" could not be created and returned error code 0x80070005. Make sure that the component is registered correctly.  End Error  Error: 2009-04-04 00:07:54.48     Code: 0xC0048021     Source: Prepare and Write to Text File Export data to Text File [1724]     Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "Flat File Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0".  End Error  Error: 2009-04-04 00:07:54.48     Code: 0xC0047017     Source…  The package execution fa…  The step failed.

  2. Ramoji says:

    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

  3. Kevin says:

    After struggling forever with getting a package to run through SQL Agent, I just copy/paste the Command Line into a batch file DTEXEC /DTS ….. and use Windows Scheduler, it’s all good.

  4. Faisal says:

    HI,

    I have created a package and it works fine. The package has DataFlow in which source is connected to the Server database. The database is in MS Access 97-2003 (.mdb). I have defined this pakage in a SQL server agent job. but every time i try to execute the job, it fails. It gives this error message.

    ll rights reserved.    Started:  3:40:29 PM  Error: 2009-07-30 15:40:30.50     Code: 0xC0202009     Source: Package_ITCDS Connection manager "FA DATA"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "The Microsoft Jet database engine cannot open the file ‘\SERVER ADDRESSgroupopsDEMFINAIDseriesFA DATA.mdb’.  It is already opened exclusively by another user, or you need permission to view its data.".  End Error  Error: 2009-07-30 15:40:30.50     Code: 0xC020801C     Source: COPY_FA DATA FROM W TO SQL SERVER OLE DB Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "FA DATA" failed …  The package execution fa…  The step failed.

    For this I have changed DelayValidation property to True for every thing that is the PAckage, the Data Flow component and other component that i have. but still gave me the same error.

    Any help on this will be highly appreacited..

    tahnks,

    Faisal

  5. John says:

    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!

  6. Guru says:

    Whether the best practice of setting DelayValidation = true can be extended to all tasks created in a package.Any NI of it.

  7. Ramoji says:

    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 🙂

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

  9. Shankar says:

    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

  10. Shankar says:

    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

  11. rekha says:

    hey thanx…it worked for me…:)

  12. Sandeep says:

    I am also facing the same error even after setting DelayValidation to true!! please help.

  13. ETL vs ELTL says:

    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";"

  14. Arun says:

    I am facing issues while loading data from all excel files in a folder in SQL Server 2008. I had changed the connection manager in the Expression of the Excel connection manager to load data from all excel files in a folder dynamically. I changed the Run64BitRunTime to False after reading some blogs for this issue.

    I got the following error.

    Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTON FROM CONNECTION MANAGER. The AcquireConnection method call to the connection manager "ExcelInputFile" failed with error code 0xC0209302. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    Error: component "DFT_ExcelSrc" failed validation and returned error code 0x020801C.

    Error: One or more components failed validation.

    Error: There were errors during task validation.

    Error:  SSIS Error Code DTS_E_OLEDBNOPROVIDER_ERROR. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error code: 0x00000000. An OLE DB record is availabe. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154

    Description: "Class not registered"

    My Connection String ::

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  @[User::vG_SourceFileFolder]   + @[User::vG_SourceFileName] +";Extended Properties="EXCEL 12.0;HDR=YES";"

    I also tried changing the connection string from ACE to Jet. I got the following error after changing.

    Changed Connection String ::

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::SourceFileName] + ";Extended Properties="Excel 8.0;HDR=YES";"

    Error ::

    [Excel_SRC_BudgetFile [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "cEXC_BudgetFile" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    [SSIS.Pipeline] Error: component "Excel_SRC_BudgetFile" (1) failed validation and returned error code 0xC020801C.

    [SSIS.Pipeline] Error: One or more component failed validation.

    Error: There were errors during task validation.

    [Connection manager "ExcelInputFile"] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "External table is not in the expected format.".

  15. Anon says:

    Thanks for the help

  16. Swapnil says:

    Hi All,

    We are using Execute Package Task and getting the same error while executing the master package and calling the child package.

    I have come across a blog which says that DelayValidation = True should not be specified if we are using  Execute Package Task.

    Since the package configurations in the child package needs to be validated, the DelayValidation = True in child packages will cause other validation problems.

    Kindly let me know what could be done.

    Thanks,

    Swapnil

  17. Bhargav says:

    I had the same eerror and i changed the DelayValidation Property to TRUE and am still getting the same error.

  18. laurens!! says:

    change the transactionOption to: NotSupported  !!  And delayValidation to TRUE

  19. ram says:

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

  20. Saswata Nayak says:

    Some times the package Execution Mode gives the same Error. so Project  ->Properties-> Debugging-> Run64BitRunTime =False Will work and it worked for me.

  21. Guille says:

    Saswata Nayak: thanks a lot!. You're on fire!!.

  22. rjq says:

    I was getting this same error when dubugging in vs and it has been driving me nuts all morning.  Non of the above worked….of course it was something stupid I did.  

    One of my packages has a connection to sybase using a specific name and password.  A month ago when copying this project to another test server I changed the ProtectionLevel on the project and all the packages to "DontSaveSensitive"  So when I was debugging this package it apparently had no password for the connection.  Changing this property back to EncryptSensitiveWithUserKey solved my problem.

  23. None says:

    The information you passed with DelayValidation is incorrect.

  24. jyothirmai says:

    The First RCA is very good it worked for me

  25. Venkat says:

    Thanks. The above information really helped to me and my issue got resolved.

  26. Anon says:

    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.

  27. Saif says:

    Thank you very much..Very helpful post

  28. Harry says:

    The solution above worked for me as well

  29. Rudi says:

    Having the destination file highlighted in file explorer caused this error. "Unselecting" the file solved the problem.

  30. Pankaj says:

    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.