Ramoji Ryali's Fundas

The interesting things I experimented with SQl Server.

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.

Summary: As a best practice, set DelayValidation property to true for all the DFT tasks in a SSIS package.

Published Tuesday, March 10, 2009 6:15 AM by Ramoji

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Bulent said:

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: DOMAIN\DOMAINACCOUNT. ...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.

April 4, 2009 2:18 AM
 

Ramoji said:

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

April 4, 2009 10:32 AM
 

Kevin said:

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.

June 19, 2009 10:54 AM
 

Faisal said:

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 ADDRESS\group\ops\DEM\FINAID\series\FA 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

July 30, 2009 4:45 PM
 

John said:

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!

December 5, 2009 7:29 PM

Leave a Comment

(required) 
(optional)
(required) 

  
Enter Code Here: Required
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

© 2009 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Microsoft
Page view tracker